What is DAX?

Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. DAX formulas include functions, operators, and values to perform advanced calculations and queries on data in related tables and columns in tabular data models. Microsoft designed DAX to manipulate data various applications.

In essence, DAX is used to create functions in the form of either calculated columns or calculated measures. This may be a familiar term if you’ve had experience of creating calculated fields whilst using pivot tables in Excel.

Calculated Measures

Calculated measures are the way of defining calculations in a DAX model, which allows us to calculate values based on each row. Another way of describing this is by saying that it provides aggregate values from multiple rows from a table. 90% of the time, measures is what you want rather than calculated columns.

The results of measures are always changing in response to your interaction with your reports, allowing for dynamic data exploration.

Margin Pct = DIVIDE ( Sales[Margin], Sales[Sales Amount] )

Using the example above, when this “Margin Pct” measure is used in a report, and then the “Product Category” column from a Product table is used in Filters, the margin % is calculated and displayed for each product category. This is what is meant when we say that the calculated results of measures are always changing in response to your interaction with your reports and it is a dynamic aggregate based on how the data is sliced.

Creating a Calculated Measure in Power BI

1. In the Fields pane, right-click on the table where you want your measure to reside in and select New Measure.
2. The formula bar appears along the top, where you can rename your measure and enter a DAX formula.

//An example of a Calculated Measure
Total Sales = SUM(Sales[Sales Amount])

Calculated Columns

A calculated column is something that you add to an existing table and then create a DAX formula that defines the column's values. It is calculated row by row in the data table. These values are then stored within the data model. The more calculated columns you have, the larger your data model becomes.

A reason to create a calculated column is to have the data stored in the dataset. For example, if you wanted a column that shows the Year & Quarter for each sales row.

//An example of a Calculated Column
Year/Qtr = dDateTable[Year] & "-" & dDateTable[Quarter]

Creating a Calculated Column in Power BI

1. In the Fields pane, right-click the table where you want your column to reside in and select New Column.
2. The formula bar appears along the top of the report canvas, where you can rename your column and enter a DAX formula.

Choosing between columns & measures

They may look similar, but there’s a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row for the context. It is therefore computed once and stored in the model. This means that the more calculated columns you have, the larger your model becomes. A measure operates by aggregating the data defined by the current context, which depends on the filter used in the report. They are computed at query time and stored in the model purely as source code and is computed only when used in the report.

Summary of Differences

❤️ Enjoyed this article?

Forward to a friend and let them know where they can subscribe (hint: it's here).