The DAX Guide
DAX: Data Analysis Expressions
DAX is a formula and query language used in Power BI to create custom calculations, measures, and calculated tables. They are like Excel formulas but are designed for working with relational data and large datasets.
Core Concepts of DAX:
Mastering DAX requires a strong understanding of how data is filtered and evaluated:
- Row Context: The concept of looking at a single row at a time, which is the default evaluation mode for calculated columns and iterative functions.
- Filter Context: The set of filters applied to a visual by row/column headers, slicers, and report filters before computing a value.
- Context Transition: The process of turning a row context into a filter context, which occurs automatically when you invoke a measure inside a row-by-row calculation.
DAX is mainly used to build 3 types of calculations:
1. Measures: Measures are dynamic calculation formulas where the results change depending on context. Measures are used in reporting that support combining and filtering model data by using multiple attributes such as a Power BI report. Measures are created by using the DAX formula bar in the model designer. They are calculated based on what you click, filter, or slice in your report.
Ex: Total Sales = SUM (Sales [Amount]). This calculates the total sales amount
2. Calculated Columns: A calculated column is a column that is added to an existing table. Static data that is calculated row-by-row is physically stored in your data model. Column values are only recalculated if the table or any related table is processed (refresh) or the model is unloaded from memory and then reloaded
Ex: Full Name = [First Name] & " & [Last Name] OR Full Name = CONCATENATE([First Name], " " & [Last Name])
This combines 'First Name' and 'Last Name' into 'Full Name'
3.Calculated Tables: A calculated table is a computed object, based on a formula expression, derived from all or part of other tables in the same model. Instead of querying and loading values into your new table's columns from a data source, a DAX formula defines the table's values. A new table is created from existing data using DAX formulas. These tables are stored in the data model and refresh when the dataset refreshes. Entire table can be generated by joining or filtering existing data.
Tables can be created with below options:
a. Copying Existing Table: This is used to create separate versions of an existing table.
Ex: Patient_Copy = Dim_Patient
b. Summarizing Data: This is used to create a summary table that groups your data and computes aggregations like sums or counts into a new table.
Ex: Count patients by gender - Patient_Gender_Summary = SUMMARIZE (Dim_Patient, Dim_Patient[Gender], "Patient Count", COUNT(Dim_Patient[PatientID]))
c. Filtering Existing Data: Extracts specific rows from an existing table based on a logical condition. Used for creating a table containing only certain records.
Ex: Diabetic patients only Table - Diabetic_Patients = FILTER (Fact_Patient, Fact_Patient[Diabetes] = "Yes")
d. Distinct/Unique Values: Creates a single-column table containing only unique values from a specific column. Ex: Unique_Cities = DISTINCT(Dim_Patient[City])
e. Select Specific Columns: Create a smaller table useful for simplified reporting.
Ex: Patient_Basic_Info = SELECTCOLUMNS (Dim_Patient, "Patient ID", Dim_Patient[PatientID], "Full Name", Dim_Patient[Full Name], "Gender", Dim_Patient[Gender])
f. Add New Calculated Columns Inside a Table: Using ADDCOLUMNS.
Ex: Add BMI category - Patient_BMI_Category = ADDCOLUMNS (Dim_Patient, "BMI Category", IF(Dim_Patient[BMI] >= 25, "Overweight", "Normal”))
g. Calendar / Date Table (Very Common): Important for time intelligence used for YTD calculations, Monthly trends, Time analysis.
Ex: Date_Table = CALENDAR (DATE (2024,1,1), DATE (2026,12,31)) Or dynamic: Date_Table = CALENDARAUTO ()
h. Cross Join Two Tables: Combine every row from two tables creating all possible combinations.
Ex: Region_Product = CROSSJOIN (Dim_Region, Dim_Product)
i. Union Multiple Tables: Append tables together like Append Queries in Power Query.
Ex: Combined_Data = UNION (Table1, Table2)
j. Top N Table: It is useful for ranking and dashboards Ex: Top 10 patients by glucose level
Ex: Top10_Glucose = TOPN(10, Fact_Patient, Fact_Patient[Glucose], DESC)
Difference: Calculated Table vs Measure vs Column
Feature | Calculated Table | Calculated Column | Measure |
Creates new table | ✓ | ✗ | ✗ |
Creates new column | ✗ | ✓ | ✗ |
Dynamic with filters | Limited | No | Yes |
Stored in Model | ✓ | ✓ | No |
Performance Optimization Best Practices
Use Power Query First: Transform, clean, or group raw rows before loading data; do not use DAX calculated columns for basic cleanup.
Declare Variables: Use VAR to save the result of a sub-calculation. This eliminates redundant evaluations and speeds up performance.
Avoid Naked Columns: Always wrap column references in an explicit aggregator (like SUM) when building measures to prevent errors.
Prefer Measures Over Columns: Prioritize building dynamic measures to minimize file sizing and maximize dynamic responsiveness.
Test with DAX Query View: Use the Power BI DAX Query View, the built-in tool to test individual equations and isolate slow performance before writing them into production reports.
DAX Functions: Functions are used by Data Engineers to enhance their data processing and analysis capabilities in Power BI.
Built in Functions in DAX:
Category | Examples |
Aggregation | SUM, AVERAGE, COUNT |
Logical | IF, SWITCH |
Text | CONCATENATE, LEFT |
Date & Time | TODAY, YEAR |
Filter | FILTER, ALL |
Time Intelligence | TOTALYTD, SAMEPERIODLASTYEAR |
Popular DAX Functions:
Below are some very commonly used DAX Functions:
Function | Purpose | Key Features |
CALCULATE | Modifies filter context | 1.Alters filter context for expressions 2.Enables dynamic calculations 3.Supports multiple filter arguments
|
SUMX | Iterative sum calculation | 1.Iterates over each row in a table 2.Performs custom calculations on each row 3.Sums the results of the calculations
|
FILTER | Creates filtered table | 1.Creates a new table based on conditions 2.Supports complex filtering logic 3.Can be used within other functions
|
RELATED | Retrieves values from related tables | 1.Retrieves values from related tables 2.Enables cross-table calculations 3.Supports one-to-many relationships
|
DISTINCTCOUNT | Counts unique values | 1.Tallies every unique entry in a single column, ignoring any duplicate repetitions |
ALL | Removes filters | 1.Returns all rows in a table or all unique values in a column, completely ignoring any filters that might have been applied. |
DATEADD | Time intelligence calculations | 1.Shifts date forward or backward in time 2.Supports various date intervals 3.Enables dynamic time-based comparisons
|
RANKX | Ranking function | 1.Ranks items based on a specified expression 2.Supports various ranking methods 3.Identifies top or bottom performers
|
CROSSFILTER | Modifies relationship filters | 1.Modifies filter direction of relationships 2.Enables bi-directional filtering 3.Supports dynamic relationship management
|
SUMMARIZE | Creates summary tables | 1.Creates summary tables 2.Supports group-by operations 3.Enables multi-level aggregations
|
ADDCOLUMNS | Adds calculated columns | 1.Adds new columns to existing tables 2.Supports complex calculations 3.Enables dynamic table extensions
|
TREATAS | Applies dynamic filters | 1.Applies table expression results as filters 2.Enables dynamic filtering scenarios 3.Supports complex data model interactions
|
Difference Between Excel Formulas and DAX:
Excel | DAX |
Cell-based | Column/table-based |
Works on sheets | Works on data models |
Simpler context | Advanced filter context |
Smaller datasets | Large-scale analytics |
Real World Uses:
Businesses use DAX to calculate:
Revenue trends
Customer retention
Inventory turnover
Financial KPIs
Forecasts
Time-based comparisons
Why DAX is Powerful:
DAX features a vast library of over 200 functions. Some of its most powerful capabilities include:
Time Intelligence: Easily calculate metrics like Year-to-Date (YTD) totals, rolling averages, or sales from the same period last year.
Filter Context: DAX automatically recalculates values based on the filters or slicers the user interacts with on the report canvas.
Row Context: Ability to perform step-by-step logic, evaluating data row-by-row before aggregating the final result.
