DAX (Data Analysis Expressions) is a formula language used in Microsoft Power BI, Power Pivot, and Analysis Services Tabular Models to perform data modeling, calculations, and analysis.
It’s similar to Excel formulas but designed for working with data models and relationships, not just individual cells.
What DAX Does
DAX helps you:
- Create calculated columns and measures (custom calculations).
- Build aggregations (sums, averages, counts, etc.) dynamically.
- Perform time intelligence (like Year-to-Date or Month-over-Month comparisons).
- Filter and manipulate data contextually using relationships between tables.
Examples of DAX Formulas
| Purpose | Example | Description |
| Basic Calculation | Total Sales = SUM(Sales[Amount]) | Adds up all sales amounts. |
| Conditional Logic | Profit = IF(Sales[Amount] > 0, Sales[Amount] – Sales[Cost], 0) | Calculates profit only if sales > 0. |
| Time Intelligence | YTD Sales = TOTALYTD(SUM(Sales[Amount]), Dates[Date]) | Calculates year-to-date sales. |
| Filter Calculation | Sales Last Year = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Dates[Date])) | Compares current and previous year sales. |
Where DAX Is Used
- Power BI: to build dashboards and reports with dynamic calculations.
- Excel (Power Pivot): to enhance pivot tables and models.
- SQL Server Analysis Services (Tabular): to define KPIs and metrics for enterprise BI.
Key Concepts in DAX
- Row context: applies when calculating row-by-row values (like a calculated column).
- Filter context: applies when filters are used in visuals or functions like CALCULATE().
- Evaluation context: the combination of row and filter contexts that determines the result.
Can you show me a simple Power BI example of how DAX is used in a report?
Let us walk through a simple Power BI example showing how DAX is used to calculate Year-over-Year (YoY) Growth for sales.
Scenario
You have a Sales table with these columns:
- Date
- Product
- SalesAmount
And a Dates table linked to your Sales data (for time intelligence).
Step 1: Create Total Sales Measure
In Power BI, go to the Modeling tab → click New measure → enter:
Total Sales = SUM(Sales[SalesAmount])
This measure sums all the sales in your data model.
Step 2: Create Last Year’s Sales Measure
Now create another measure:
Sales Last Year = CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(Dates[Date])
)
This tells Power BI:
“Calculate Total Sales again, but shift the date context to the same period last year.”
Step 3: Create YoY Growth %
YoY Growth % =
DIVIDE(
[Total Sales] – [Sales Last Year],
[Sales Last Year],
0
)
This calculates the percentage growth from last year to this year.
The DIVIDE() function safely handles division by zero.
Step 4: Add to Report
- Add a Table or Line Chart visual in Power BI.
- Use:
- Dates[Year] (or Dates[Month]) as the axis
- [Total Sales], [Sales Last Year], and [YoY Growth %] as values
- Dates[Year] (or Dates[Month]) as the axis
Now your report will dynamically calculate and show:
- Total Sales per year
- Sales from the previous year
- % Growth year-over-year
Example Output
| Year | Total Sales | Sales Last Year | YoY Growth % |
| 2023 | $2,000,000 | $1,750,000 | 14.3% |
| 2024 | $2,500,000 | $2,000,000 | 25.0% |