DAX Basics: Measures and Calculated Columns
DAX stands for Data Analysis Expressions. It is the formula language used in Power BI, Analysis Services and Power Pivot in Excel. A DAX formula combines functions, operators and values to calculate a result from the data in your model.
Referring to data
- A table is written by name:
Sales. Names that contain spaces go in single quotes:'Sales Orders'. - A column is written with its table:
Sales[Amount]. - A measure is written in square brackets:
[Total Sales].
Measures
A measure is a calculation that is evaluated when it is used in a report. Its result changes with the context: the filters, slicers, rows and columns that apply to the cell being calculated.
Total Sales = SUM ( Sales[Amount] )Place this measure in a table visual next to Product[Category] and it returns the total for each category. Place it in a card and it returns the grand total. You wrote one formula; the filter context does the rest.
Average Order Value = DIVIDE ( [Total Sales], COUNTROWS ( Sales ) )DIVIDE handles division by zero by returning a blank or an alternate result that you specify, instead of an error.
Calculated columns
A calculated column is evaluated row by row and stored in the table. Use one when you need a value for every row, for example to put it on an axis or in a slicer.
Line Total = Sales[Quantity] * Sales[Unit Price]Measure or column?
| Measure | Calculated column | |
|---|---|---|
| Evaluated | When used in a visual | Row by row, then stored in the model |
| Context | Filter context | Row context |
| Typical use | Totals, averages, ratios | Categories, flags, per-row values |
CALCULATE
CALCULATE evaluates an expression in a modified filter context.
Laptop Sales = CALCULATE ( [Total Sales], Product[Category] = "Laptop" )This returns total sales for the Laptop category, whatever category filter is applied to the visual.