Beginner's Guide to DAX | Power BI Tutorial

Introduction to DAX in Power BI

Why is DAX so important?

• What is the revenue generated in each region this month?

• What is the sales growth compared to last year?

• Which products contribute the most to my overall revenue?

The key benefits of DAX

1. Automation of complex calculations: instead of creating dozens of columns or manual queries, a single DAX measure can adjust to different filters and provide dynamic results.

2. Creating business indicators (KPIs): gross margin, conversion rate, annual growth… these are indicators you can calculate and track directly in your dashboards.

3. Simplified time-based analyses: thanks to so-called Time Intelligence functions, it becomes easy to compare a given period to another (previous month, same month last year, cumulative since the beginning of the year).

4. Dynamic and interactive models: a DAX formula does not yield a fixed result, it adapts in real-time to the filters applied in your visuals or segments (by product, by region, by period, etc.).

Understanding the fundamental concepts of DAX

1. Row context

Profit = Sales[Revenue] - Sales[Cost]

Total Profit = SUMX(Sales, Sales[Revenue] - Sales[Cost])

2. Filter context

• A Power BI visual (for example, a chart filtered by region or period).

• A slicer chosen by the user.

• A relationship between tables that restricts visible values.

• DAX functions like CALCULATE or FILTER that explicitly modify the active filter.

Sales 2024 = CALCULATE(SUM(Sales[Amount]), YEAR(Calendar[Date]) = 2024)

3. Calculation context

• Filter context selects only the sales from the first quarter of 2024.

• Row context calculates revenue product by product.

• Together, they define the calculation context.

Sales Growth = 
VAR PrevYearSales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))
RETURN DIVIDE(SUM(Sales[Amount]) - PrevYearSales, PrevYearSales)

• Filter context limits the analysis to the period displayed in the visual.

• Calculation context adds a comparison with the same period from the previous year.

The main categories of DAX functions

Aggregation functions

• SUM: adds up the values in a column. Example: <code class="inline-code">SUM(Sales[Amount]) calculates the total revenue.
• SUMX: performs an iterative sum, useful when the total depends on multiple columns. Example: <code class="inline-code">SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) calculates the total based on quantity × price.
• AVERAGE: calculates the simple average of a column. Example: <code class="inline-code">AVERAGE(Sales[Amount]).
• AVERAGEX: similar to SUMX but for averages, allowing for row-by-row iteration. Example: <code class="inline-code">AVERAGEX(Products, Products[Price] * Products[Discount]).
• COUNT: counts the non-empty values in a column.
• DISTINCTCOUNT: counts only unique values, very useful for identifying the number of distinct customers, for example.

Filter and context functions

• CALCULATE: the 'king' of DAX functions. It modifies the filter context before performing the calculation. Example: <code class="inline-code">CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Electronics") calculates only electronic sales.
• FILTER: returns a filtered table. Example: <code class="inline-code">FILTER(Sales, Sales[Amount] > 1000) keeps only sales over 1000.
• ALL: removes all filters applied to a table or column, often used to compare an individual value to a global total.
• ALLEXCEPT: removes all filters except those specified. Example: <code class="inline-code">ALLEXCEPT(Sales, Sales[Product]) keeps the product filter but ignores others.
• VALUES: returns a unique list of values from a column. Useful for creating implicit relationships or counting distinct categories.

Relationship and lookup functions

• RELATED: fetches a value from a related table (one-to-many relationship). Example: <code class="inline-code">RELATED(Customers[Region]) displays the region of the customer linked to each sale.
• RELATEDTABLE: returns all rows from a related table, often used in measures.
• LOOKUPVALUE: searches for a value in a column based on criteria. Example: <code class="inline-code">LOOKUPVALUE(Products[Price], Products[ProductID], Sales[ProductID]) retrieves the price of a product corresponding to a sale.

Time-based functions (Time Intelligence)

• DATEADD: shifts a period in time (days, months, quarters, years). Example: comparing current month sales with the previous month.
• SAMEPERIODLASTYEAR: returns the same period but from the previous year, perfect for comparing this year vs last year.
• TOTALYTD / TOTALQTD / TOTALMTD: calculate totals from the beginning of the year, quarter, or month up to the selected date.
• PARALLELPERIOD: shifts a period while maintaining granularity. Example: comparing January 2024 to January 2023.

Statistical and logical functions

• IF: applies a simple condition. Example: <code class="inline-code">IF(Sales[Amount] > 1000, "Large Sale", "Small Sale").
• SWITCH: replaces multiple nested IF conditions with a more readable structure. Example: categorizing sales by thresholds.
• DIVIDE: performs division while automatically handling division by zero. Example: <code class="inline-code">DIVIDE(Sales[Profit], Sales[Revenue]) calculates margin without risk of error.
• RANKX: assigns a dynamic ranking based on a calculation. Example: <code class="inline-code">RANKX(ALL(Products), SUM(Sales[Amount])) ranks products by their revenue.

In-depth practical examples

1. Calculating annual growth (YoY Growth)

YoY Growth =
DIVIDE(
    SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date])),
    CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))
)

2. Creating a dynamic product ranking

ProductRank = RANKX(ALL(Products), SUM(Sales[Amount]), , DESC)

3. Determining a product's market share

MarketShare =
DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales)))

4. Analyzing sales from loyal customers

FilteredSales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(Customers, Customers[LoyaltyStatus] = "Loyal")
)

5. Cumulative sales since the beginning of the year

YTD Sales = TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

Best practices for writing in DAX

1. Favor measures over calculated columns.

2. Use variables (VAR) for improved readability and performance.

3. Optimize the data model: fewer columns, more well-defined relationships.

4. Name your measures clearly: for example Total Sales instead of Measure1.

5. Test your formulas step by step by creating intermediate measures.

Tips and errors to avoid

• Don't forget to create a calendar table to leverage time functions.

• Be cautious of implicit filters: a common mistake is to get an empty or incorrect result because CALCULATE modifies the context unintentionally.

• Avoid creating too many calculated columns that burden the model.

• Always check your results with smaller datasets before applying your measures to millions of rows.

Advanced use cases

1. Complex Time Intelligence scenarios

2. Dynamic segmentation

3. Performance analysis

Conclusion and next steps

• to use basic functions (SUM, AVERAGE, CALCULATE),

• to leverage time functions to compare different periods,

• to write more advanced measures (RANKX, ALL, SWITCH),

• and to optimize your models for performance.

Next steps:

• Explore resources like DAX Guide.

• Practice regularly with your own data.

• Participate in Power BI communities (forums, LinkedIn, Meetup).

• Dive into real-world projects by applying DAX to actual business scenarios.