What is DAX?

DAX stands for Data Analytics Expressions, a formula expression and query language designed for working with tabular models and provides specialized syntax for querying and analysis. It is used mainly in Power BI along with Excel Power Pivot and SQL Server Analysis Services (SSAS) to perform advanced calculations and queries on data.

DAX formulas typically are made up of a wide range of functions, operations and constants that are then evaluated. In contrast to DAX calculation formulas, DAX queries can be run for Analysis Services Multidimensional models. DAX provides analysts with a robust toolkit for comprehensive data analysis. It empowers users to conduct year-over-year comparisons and facilitates the creation of custom ratios and metrics, advanced filtering and aggregation. Analysts can leverage DAX to create calculated tables to seamlessly combine, transform and manipulate data from varied sources. Furthermore, it offers an advanced row-level security feature, ensuring controlled access to sensitive data based on user roles and facilitating a wide array of analytical capabilities.

The three basic components of DAX formula are –

1. Syntax – The DAX syntax consists of various elements that help make up the basic formula and how it is written. It is similar to Excel formulas except it has some additional rules due to its focus on working with tabular data models.
2. Function – Functions are the building blocks of the DAX formula. They are parameterized DAX formulas that perform a specific calculation and can be categorized based on their functionality. DAX includes a pre-built library of more than 200 functions, operators and constructs to perform calculations for any kind of data analysis needed.
3. Context – Defining where the calculations need to be performed makes the context crucial for DAX. The DAX context is evaluated within row context and filter context, with the former referring to the selected rows of the table and latter referring to the subset rows or columns as part of the selected rows in the row context.

Why is DAX important to Power BI?

Using DAX formulas within the Power BI environment can help maximize the full potential of the platform. The calculation engine of Power BI is efficient enough to yield results for basic querying and calculations. But they are incapable of providing deeper insights into the data and thus, DAX formulas become the key to advanced estimations.

DAX formulas increase the efficiency and functionality of the report generated by the tool. DAX formulas become more valuable and applicable in a case where the same calculation needs to be performed across various tables and charts within data. Changing one formula is better than changing individual formulas in all places it has been used. The flexibility of DAX formulas allows users to run unique and customized calculations that can solve specific business problems.

DAX expands the utility of Power BI beyond simple calculations and provides an opportunity to create experimental and innovative visualizations. Complex logic and aggregations can be easily visualized using DAX formulas.

Understanding Context in DAX Formulas

The context is critical to the DAX syntax as the DAX formulas change according to the context of their creation. Avoiding unnecessary errors is part of understanding context in DAX formulas. There are two types of DAX context – row context and filter context.

Row Context – In row context, the standard reference of DAX formulas is the row under consideration. It will use the rows of all the columns in the current table and include columns only from related tables. The formula with the row context is inherent for the calculated column. The calculation for each row is presented within the calculated column, making row context default for such calculations. In the case of measures, all the rows are aggregated simultaneously, eliminating the need for row context. However, to define row context in a measure, a function called iterator is used, which will first perform row-by-row calculations and aggregate the new results as per the requirement.

Filter Context – The filter context is applied on top of the row context, allowing the user to specify the rows or columns that should be evaluated. They put constraints on the values retrieved from each column and row. The filter context can either be applied through the report filters on each column and row header or by specifying filter expressions in the formula itself. The filter context is useful to filter entire tables, attain context for values under evaluation or deselect certain columns. One way to apply filter context in the formula is through the ‘CALCULATE’ function that allows the addition of filter parameters in the expression.

The DAX formula requires an assessment of the rows and columns named in the formula to determine if they exist in the data set or not. The current context of the formula might include columns from related tables and all the filters that have been applied to it.

Where are DAX Formulas Used in Power BI?

DAX formulas are used to perform calculations to be implemented four ways – measures, row-level security, calculated columns and calculated tables.

Measure – These are ever-changing calculations and based on the context provided for the calculation, the resultant values will also experience changes. The DAX formula bar supports the creation of measures. The formulas created in the measure calculation can have either Auto sum functions or can be customized using the DAX formula bar. To generate results for a measure, context is imperative to decide which data is to be extracted. The relevant cells need to be defined so that the data for which the measure is executed runs separate queries for each cell. The results will show calculation for each subset of the data table in question.

Row-level Security – The DAX formula with row-level security results in a filtered-out evaluation of the data. The rows which need to be calculated can be defined, and the rest are not returned as part of the calculation. In the DAX formula, the allowed rows are accessible based on the role of the user. Row-level security will include roles for accessing certain row sets preventing unauthorized access.

Calculated Columns – Through the calculated columns in the DAX formula, an additional column is added to the existing table. This calculated column is the result of the definition put in the DAX formula. It is evaluated for each row of the table and its values are stored in the tabular model’s in-memory. The context for this type of calculation is expressed in the DAX formula.

Calculated Tables – The calculated table is formulated based on the entirety or parts of the existing table. The DAX formula determines the values of this table and eliminates the need to derive values from a data source. Therefore, the calculated table is separated and allows users to manipulate values without disturbing the original table.

Writing DAX Queries

Basic Queries –

DAX queries are simple statements with a standard syntax that at the most basic level starts with the EVALUATE keyword. To perform a specific application for any keyword, functions of DAX can be nested within the query statement. These are useful when the user wants to perform multiple operations on the data.

Example – A table is named Sales with columns Date, Product, and Amount. To retrieve the total sales amount for each product, the DAX query might look like this:

EVALUATE

SUMMARIZE(

Sales,

Sales[Product],

“Total Sales”, SUM(Sales[Amount]) )

Here, ‘EVALUATE’ indicates the start of the query and ‘SUMMARIZE’ aggregates data based on specified columns. The context indicates which table and column need to be evaluated, and in this case, it is the Sales table and Product column.

‘Total Sales’ is the alias for the calculated column that will contain the total sales for each product and ‘SUM(Sales[Amount])’ is the aggregation function (sum) applied to the Amount column to calculate the total sales.

Arranging Results –

The results from the basic queries might come out in random order. To rearrange the results into a sequence or by categories, an optional clause can be added at the end of the query statement that is ORDER BY keyword.

Example – The ‘ORDER BY’ keyword is added at the end to sort the results in descending order based on the calculated column “Total Sales”. To sort the data in ascending order, the ASC keyword can be used.

EVALUATE

SUMMARIZE (

Sales,

Sales[Product],

“Total Sales”, SUM(Sales[Amount]) )

ORDER BY

[Total Sales] DESC

Further Calculations –

For adding further calculations to the query, the optional keyword DEFINE can be used at the beginning of the query statement. These calculations exist only for the duration of the query so that these ad hoc calculations have no impact on the original structure of the data model. Definitions can be variables, measures, tables and columns.

Evaluating a Single Value –

By default, measure returns a single scalar value and will cause a syntax error. So, in case the user wants to return the calculations of a single measure, the table constructor syntax must be added to avoid errors. The table constructor syntax returns a table of one or more columns. Either the curly brackets are used for the table constructor syntax or the ROW function can be used.

For example, the table constructor syntax can be used as shown –

EVALUATE
{ SUM (Sales [Sales Amount])}

This will give results in a tabular format without causing any errors.

Using Parameters in DAX Queries

A unique feature of DAX queries is that it aids well defined query statements to be parameterized. The parameters can be defined and assigned a value within the Parameters Element collection. Nomenclature for each parameter can be assigned against its values. The name of the parameter can be prefixed with the ‘@’ character and wherever the parameter is to be utilized in the syntax, it can be defined with a parameter call. This will execute the value assigned for that parameter.

For instance, to create a parameterized query to filter the data based on a selected product –

1. Define the parameter –

DEFINE

MEASURE Sales[TotalSales] =

CALCULATE(Sales[Product] = @SelectedProduct )

2. Use the parameterized measure in a query –

EVALUATE

SUMMARIZE(Sales, Sales[Product],

“Total Sales”, [TotalSales])

The @SelectedProduct parameter is used to filter the total sales for a specific product. When the query is run, enter a specific product name for @SelectedProduct parameter. The query will dynamically filter the results and provide total sales for that product.

Conclusion

DAX is a powerful and flexible formula and querying language which is easy to learn and execute. DAX plays a crucial role in helping organizations make better business decisions and gain insights from their data. In a data model, interactions between tables can be defined using DAX. This allows organizations to view their data in an organized and interconnected way, which facilitates more complex analysis and accurate reporting. The language can be leveraged to create custom analytics solutions in Power BI and related environments. This flexibility is crucial for creating meaningful metrics that align with business objectives.