What is DAX?
DAX is a Power BI built-in formula language and stands for Data Analysis Expressions. It is mainly used to create custom calculations to answer specific questions that most basic functions cannot. DAX has a similar feeling to Excel formulas, but it's designed specifically for relational data models. Naturally, the syntax has some slight differences because of that design choice. DAX has two main things it creates: measures and calculated columns. It's important to know the distinction between the two...
Measures vs. Calculated Columns
A measure is a dynamic calculation that is changes based on filters, slicers, and context in your report. It does NOT add a column to a table. Instead, these are calculated dynamically once you utilize them in your report visuals. Because of this, there is no data stored for these measures, only the calculations themselves. Examples of this could be Total Sales, Average Order Value, and YTD Revenue. These don't need their own new column, and will be calculated depending on the context and filters applied to your report.
Example Code: Total Sales = SUM(Orders[Sales])
A calculated column adds a new static column to a table, calculated on a row level. Contrary to a measure, this data is calculated row by row once the data loads, and gets permanently stored. For example, your table has First Name and Last Name as separate columns and you want them combined into a Full Name field. The code below makes a new column for Full Name, again, calculated for each row.
Example Code: Full Name = Customers[First Name] & " " Customers[Last Name]
A good rule of thumb is that if your value needs to react to what's on the page, use a measure. If it's a fixed value per row, then use a calculated column.
When DAX is most useful
DAX is very useful in a number of different scenarios. Here's a few:
- Needing calculations that change based on filters (e.g. sales for only a specific region)
- Time calculations: YoY comparisons, running totals, same period last year, MTD/YTD
- Needing to calculate ratios or percentages that a simple aggregation couldn't do
- When you data model has multiple related tables and you need to pull values across them
- Conditional logic: if/then statements based on other fields
Important Syntax and Calculations to Know
CALCULATE()
A super important DAX function. This evaluates an expression while applying filters or context. Many powerful DAX functions include this.
Syntax: CALCULATE(<expression>, <filter1>, <filter2>, ...)
Example: Sales in NY = CALCULATE(SUM(Orders[Revenue]), Customers[City] = "New York")
SUM()/AVERAGE()/COUNT()
Basic aggregation functions.
Syntax: SUM(<column>), AVERAGE(<column>), COUNT(<column>)
Example:
Total Revenue = SUM(Orders[Revenue])
Avg Order Value = AVERAGE(Orders[Revenue])
Total Orders = COUNT(Orders[OrderID])
FILTER()
Returns a filtered version of a table. Often used in CALCULATE() functions.
Syntax: FILTER(<table>, <condition>)
Example: High Value Sales = CALCULATE(SUM(Orders[Revenue]), FILTER(Orders, Order[Revenue] > 500))
CROSSFILTER()
Used inside CALCULATE() to modify the filter direction of a relationship between two tables for a specific calculation. This lets you control whether filters flow one way, both ways, or not at all between tables, but doesn't permanently changed your data model. Useful for when the default filter direction isn't giving the result you need for a specific measure.
Syntax: CROSSFILTER(<left column>, <right column>, <direction>)
Direction Options: BOTH | NONE | ONEWAY
Example: Sales with both filter directions = Calculate(SUM(Orders[Revenue]), CROSSFILTER(Customers[CustomerID], Orders[CustomerID], BOTH))
IF()
Conditional logic, similar to Excel.
Syntax: IF(<condition>, <value if true>, <value if false>)
Example: Order Size = IF(Orders[Quantity] > 10, "Large Order", "Standard Order")
DIVIDE()
Best practice to use instead of "/", handles divide-by-zero errors.
Syntax: DIVIDE(<numerator>, <denominator> , <alternate result if zero>)
Example: Profit Margin = DIVIDE(Orders[Profit], Orders[Revenue], 0)
TOTALYTD() & SAMEPERIODLASTYEAR()
Time intelligence functions for period comparisons.
Syntax:
TOTALYTD(<expression>, <dates column>)
CALCULATE(<expression>, SAMEPERIODLASTYEAR())
Examples:
YTD Revenue = TOTALYTD(SUM(Orders[Revenue]), Dates[Date])
Sales Last Year = CALCULATE(SUM(Orders[Revenue]), SAMEPERIODLASTYEAR(Dates[Date]))
Closing
DAX has somewhat of a learning curve, but it unlocks a ton of analytical potential once understood. With DAX as well, it's important to understand when to need it vs. when just native Power BI visuals or simple aggregations are enough. Knowing your data model is also very important to make proper DAX calculations, especially for filtering, cross filtering, and any other functions related to how your data is structured.