Power BI DAX Introduction
What is DAX?
DAX stands for Data Analysis Expressions.
It is the formula language of Power BI.
You use DAX to add calculations to your data model: new columns, totals, counts, ratios and much more.
DAX is also used in Power Pivot in Excel and in SQL Server Analysis Services, so what you learn here works there too.
Here is a simple DAX formula.
It adds up the Amount column of the Sales table:
Example
Total Sales = SUM(Sales[Amount])
You will create this exact formula in the DAX Measures chapter.
You Have Already Used DAX
In the Date Table chapter, you created the Date table with this formula:
Date = CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31))
Then you added a Year column to it:
Year = YEAR('Date'[Date])
Both formulas are DAX.
The first one creates a whole table.
The second one adds a column to a table.
Where DAX Is Used
In Power BI Desktop, you write DAX in three places:
| Calculation | What it creates | Poke Mart example |
|---|---|---|
| Calculated column | A new column in a table. The formula is calculated once for every row. | Sales[Amount], the quantity times the unit price |
| Measure | A calculation that returns one value, such as a total, for whatever a visual shows. | Total Sales, Profit, Margin % |
| Calculated table | A whole new table. | The Date table |
Measures are the most important of the three.
Most of the DAX you write will be measures.
DAX vs Excel Formulas
If you know Excel formulas, DAX will look familiar.
Many functions have the same names: SUM, AVERAGE, MIN, MAX, COUNT, IF, LEFT, DATE and many more.
The big difference: Excel formulas work on cells. DAX works on columns and tables.
In Excel, you add up the Sales column of poke_mart_sales.xlsx (the file from First Report) with a range of cells:
Excel
=SUM(I2:I2741)
In DAX, you name the table and the column.
There are no cell addresses:
DAX
Total Sales = SUM(Sales[Amount])
Both formulas return 9,416,800.
Here are the main differences:
| Excel formula | DAX formula | |
|---|---|---|
| Works on | Cells and ranges, like I2:I2741 | Columns and tables, like Sales[Amount] |
| Starts with | An equal sign: = | A name and an equal sign: Total Sales = |
| A formula for every row | You copy the formula down the column. Each row has its own copy. | You write the formula once. It is calculated for every row of the table. |
| Look up a value in another table | VLOOKUP or XLOOKUP | A relationship and the RELATED function |
| Totals by group | A PivotTable | A measure in a visual |
A measure works much like a value in the Values area of a PivotTable.
The same measure shows a different number in every row of a visual, because each row has its own filter.
You learn how this works in DAX Measures and DAX Context.
Note: You cannot paste Excel formulas into Power BI.
DAX has no cell references and no ranges.
The Poke Mart Model
This chapter uses the data model you built in the Modeling chapter, from the file poke_mart_model.xlsx:
| Table | Columns | Rows |
|---|---|---|
| Sales | OrderID, Date, StoreID, ProductID, Quantity | 2,740 |
| Products | ProductID, Product, Category, UnitPrice, UnitCost | 10 |
| Stores | StoreID, Store, Region | 6 |
| Date | Date, Year, Quarter, Month Number, Month | 731 |
Sales is related to Products on ProductID, to Stores on StoreID, and to Date on Date.
Each Sales row is one order.
If you are starting here, build the model first: see Star Schema, Relationships and Date Table.
Note: The Stores sheet holds only text, so Power Query cannot tell its header row from the data, and Stores loads with the columns Column1, Column2 and Column3. After Load, select Transform data, select the Stores query, and on the Home tab select Use First Row as Headers.
Then select Close & Apply.
Power BI now creates the Sales to Stores relationship by itself.
See Star Schema for the details.
Notice that the Sales table has no money columns.
It only knows how many of each product were sold.
In this chapter, you use DAX to calculate the sales amounts and costs, and then totals, profit and margins.
Save the report as poke_mart_dax.pbix (File > Save as > Browse this device), and save it again at the end of each DAX page.
The Visuals chapter continues in this file.
What You Will Learn
The DAX chapter builds up one step at a time:
- Syntax: how a DAX formula is written
- Calculated columns and RELATED: add the Amount and Cost columns to Sales
- Measures and aggregation functions: Total Sales, Total Cost, Order Count and more
- DIVIDE and IF and SWITCH: Profit, Margin % and price bands
- Context, CALCULATE and the functions after it: change what a measure calculates