DAX Mastery Student Handbook

1. How to Use This Handbook

This handbook is written as if an experienced teacher is sitting beside you and explaining every concept slowly. You do not need any programming background. We start from absolute zero.

Recommended Learning Path

1. Read each chapter in order. Do not skip the fundamentals.
2. Type every DAX formula yourself in Power BI Desktop.
3. After each example, change the numbers and observe what happens.
4. Complete the practice exercises at the end of each major section.
5. Build the Mini Project only after finishing the core chapters.
6. Use the Cheat Sheet for quick revision.

Teaching Pattern Used Throughout

For every important function we follow this exact pattern:

WHAT → WHY → SYNTAX → EVERY WORD → SIMPLE EXAMPLE → RESULT → WHY RESULT → REAL-WORLD EXAMPLE → COMMON MISTAKE → PRACTICE

2. DAX Roadmap

Here is the complete journey you will take. Each stage builds on the previous one.

3. DAX Fundamentals

3.1 What is DAX?

DAX stands for Data Analysis Expressions.

It is a formula language used to create custom calculations in Power BI, Power Pivot (Excel), and Analysis Services.

Where is DAX used?

• Power BI Desktop and Power BI Service
• Excel Power Pivot
• SQL Server Analysis Services (SSAS) Tabular models
• Azure Analysis Services

What DAX is NOT

DAX is not a programming language like Python or C#. You do not write loops or create applications with it. DAX is a calculation language designed specifically for business intelligence and analytical reporting.

3.2 Tables, Rows and Columns

Before we write any DAX, we must clearly understand the building blocks.

What is a Table? A table is a collection of related data arranged in rows and columns. In our handbook we will mostly use a table called Sales.

What is a Column? A column stores one type of information for every row. Examples: Customer, Product, SalesAmount, Region.

What is a Row? A row is one complete record. In the Sales table, each row is one sales transaction.

3.3 Our Standard Sample Dataset

Throughout this handbook we will use one consistent Sales table. Memorize its structure. All examples refer to this data.

Note: We may introduce blank values later when comparing COUNT vs COUNTA.

Quick Facts from the Sample Data

• Total rows = 10
• Regions: North, South, West
• Categories: Electronics, Furniture
• Salespersons: Ravi, Priya, Amit
• Total SalesAmount = 276,000

4. Measures vs Calculated Columns

This is one of the most important distinctions in Power BI. Many beginners confuse the two.

4.1 Calculated Column

A calculated column is stored in the table. It is calculated once for every row when the data is refreshed.

Profit =
Sales[SalesAmount] - Sales[Cost]

«Create a new column called Profit. For each row, take SalesAmount from this row and subtract Cost from this row.»

Line-by-line explanation:

• Profit — name of the new column
• = — defining an expression
• Sales[SalesAmount] — SalesAmount column from Sales table (current row)
• - — subtract
• Sales[Cost] — Cost column from Sales table (current row)

Because this runs row by row, we say it has row context.

4.2 Measure

A measure is a calculation evaluated at query time based on the filters currently applied (the filter context). Measures are not stored as columns; they are calculated on demand.

Total Sales =
SUM(Sales[SalesAmount])

«Create a measure called Total Sales. Add up all the values in the SalesAmount column (considering current filters).»

5. Filter Context

Filter context is the most important concept in DAX. If you master only one idea, master this.

5.1 Simple Meaning

Filter context means: "The filters currently affecting your calculation."

5.2 How Filter Context is Created

• Slicers on the report page
• Page-level filters
• Visual-level filters
• Rows and columns of a matrix or table visual
• Cross-filtering from other visuals
• Relationship filters from related tables
• Filters applied inside CALCULATE

5.3 Demonstration

Total Sales =
SUM(Sales[SalesAmount])

If no filters are applied, this returns the sum of all 10 rows = 276,000.

If a slicer or matrix row has Region = North, the filter context now contains only the North rows. The same measure returns only the North total.

All Sales (10 rows)
      ↓
Region = North filter applied
      ↓
Only North rows remain (4 rows)
      ↓
SUM(Sales[SalesAmount]) on those rows
      ↓
Result = North Sales

6. Row Context

Row context means Power BI knows which single row it is currently looking at.

6.1 Where Row Context Appears

• Inside calculated columns (always)
• Inside iterator functions (SUMX, AVERAGEX, COUNTX, FILTER, etc.)

6.2 Classic Example — Calculated Column

Profit =
Sales[SalesAmount] - Sales[Cost]

When this formula runs, Power BI goes to row 1, takes SalesAmount of row 1, subtracts Cost of row 1, stores the result. Then moves to row 2 and repeats. That "current row" awareness is row context.

7. COUNT Family

7.1 COUNT

What is it? COUNT counts the number of numeric (non-blank) values in a column.

Why do we need it? We often need to know how many numbers exist in a column — for example, how many quantities were recorded.

COUNT(<column>)

Total Quantities Recorded =
COUNT(Sales[Quantity])

Result = 10 (every row has a Quantity)

• Works only on numeric columns (or dates)
• Ignores blank values
• Does not count text values

Order Count =
COUNT(Sales[SalesID])

7.2 COUNTA

What is it? COUNTA counts the number of non-blank values in a column (numbers or text).

COUNTA(<column>)

Customer Entries =
COUNTA(Sales[Customer])

Result = 10

7.3 COUNTX

Functions ending with X usually iterate through a table row by row.

What is it? COUNTX goes through each row of a table, evaluates an expression, and counts how many times the expression returns a non-blank value.

COUNTX(
    <table>,
    <expression>
)

Order Count =
COUNTX(
    Sales,
    Sales[SalesID]
)

«Go through every row of the Sales table. For each row evaluate Sales[SalesID]. Count how many non-blank results you get.»

Valid Orders =
COUNTX(
    Sales,
    IF(
        Sales[SalesAmount] > 0,
        Sales[SalesID]
    )
)

7.4 COUNTROWS

What is it? COUNTROWS simply counts the number of rows in a table.

Total Orders =
COUNTROWS(Sales)

Result = 10

North Orders =
COUNTROWS(
    FILTER(
        Sales,
        Sales[Region] = "North"
    )
)

8. SUM

What is it? SUM adds up all the numeric values in a column (respecting the current filter context).

SUM(<column>)

Total Sales =
SUM(Sales[SalesAmount])

Total Cost =
SUM(Sales[Cost])

Total Profit =
SUM(Sales[Profit])

With no filters, Total Sales = 276,000

• The column must be numeric
• Blank values are treated as zero
• The result changes automatically when filter context changes

9. SUMX — The Major Iterator

This is one of the most important functions in DAX. Take your time here.

Core idea: SUM adds an existing column. SUMX calculates something for every row and then adds the results.

SUMX(
    <table>,
    <expression>
)

Total Revenue =
SUMX(
    Sales,
    Sales[Quantity] * Sales[SalesAmount]
)

«Go through every row of Sales. For each row multiply Quantity by SalesAmount. Then add all those products together.»

Why can't we just write SUM(Sales[Quantity] * Sales[SalesAmount])? Because SUM only accepts a column, not an expression. SUMX exists exactly for this reason.

10. AVERAGE & AVERAGEX

AVERAGE returns the arithmetic mean of all non-blank numeric values in a column.

Average Sales =
AVERAGE(Sales[SalesAmount])

Conceptually it is similar to SUM / COUNT (of non-blank values). Blanks are ignored. Filters change the set of values that participate.

AVERAGEX

AVERAGEX iterates a table, evaluates an expression per row, and returns the average of the non-blank results.

Average Order Value =
AVERAGEX(
    Sales,
    Sales[SalesAmount]
)

Average Line Profit =
AVERAGEX(
    Sales,
    Sales[SalesAmount] - Sales[Cost]
)

11. CALCULATE — The Heart of DAX

If you master CALCULATE, you master DAX. This is the most important chapter.

What is it? CALCULATE changes the filter context in which an expression is evaluated.

CALCULATE(
    <expression>,
    <filter1>,
    <filter2>,
    ...
)

Total Sales =
SUM(Sales[SalesAmount])

North Sales =
CALCULATE(
    [Total Sales],
    Sales[Region] = "North"
)

«Create a measure called North Sales. Calculate Total Sales, but only for the rows where Region is North.»

What happens step by step:

1. CALCULATE receives the expression [Total Sales]
2. It applies the filter Sales[Region] = "North"
3. The original filter context is modified
4. [Total Sales] is evaluated under this new filter context
5. The result is returned

Electronics Sales =
CALCULATE(
    [Total Sales],
    Sales[Category] = "Electronics"
)

Ravi Sales =
CALCULATE(
    [Total Sales],
    Sales[Salesperson] = "Ravi"
)

North Electronics Sales =
CALCULATE(
    [Total Sales],
    Sales[Region] = "North",
    Sales[Category] = "Electronics"
)

Multiple filters work together as AND conditions.

12. CALCULATE + Aggregation Functions

CALCULATE + COUNT / COUNTA / COUNTROWS

North Order Count =
CALCULATE(
    COUNT(Sales[SalesID]),
    Sales[Region] = "North"
)

North Customer Count =
CALCULATE(
    COUNTA(Sales[Customer]),
    Sales[Region] = "North"
)

North Rows =
CALCULATE(
    COUNTROWS(Sales),
    Sales[Region] = "North"
)

CALCULATE + SUM

North Sales =
CALCULATE(
    [Total Sales],
    Sales[Region] = "North"
)

South Sales =
CALCULATE(
    [Total Sales],
    Sales[Region] = "South"
)

Electronics Sales =
CALCULATE(
    [Total Sales],
    Sales[Category] = "Electronics"
)

Furniture Sales =
CALCULATE(
    [Total Sales],
    Sales[Category] = "Furniture"
)

CALCULATE + AVERAGE

North Average Sales =
CALCULATE(
    AVERAGE(Sales[SalesAmount]),
    Sales[Region] = "North"
)

CALCULATE + X Functions

North Revenue =
CALCULATE(
    SUMX(
        Sales,
        Sales[Quantity] * Sales[SalesAmount]
    ),
    Sales[Region] = "North"
)

CALCULATE
   ↓
changes filter context to North
   ↓
only North rows remain
   ↓
SUMX iterates those North rows
   ↓
calculates Quantity × SalesAmount per row
   ↓
sums the results

North Average Profit =
CALCULATE(
    AVERAGEX(
        Sales,
        Sales[SalesAmount] - Sales[Cost]
    ),
    Sales[Region] = "North"
)

13. FILTER Function

FILTER creates a table that contains only the rows that satisfy a condition.

FILTER(
    <table>,
    <condition>
)

High Value Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[SalesAmount] > 30000
    )
)

«Calculate Total Sales, but only keep the rows where SalesAmount is greater than 30,000.»

North High Value Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[Region] = "North"
            && Sales[SalesAmount] > 30000
    )
)

&& means AND. || means OR.

14. ALL Family — Removing Filters

14.1 ALL

ALL removes filters from a table or from specific columns.

All Region Sales =
CALCULATE(
    [Total Sales],
    ALL(Sales[Region])
)

«Ignore whatever Region filter is currently active. Calculate Total Sales as if no Region filter exists.»

Sales % of Total =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALL(Sales[Region])
    )
)

14.2 REMOVEFILTERS

REMOVEFILTERS is a clearer way to express the same idea as ALL when you only want to remove filters.

All Region Sales =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Sales[Region])
)

14.3 ALLEXCEPT

ALLEXCEPT removes all filters from a table except the ones you want to keep.

Sales by Category Only =
CALCULATE(
    [Total Sales],
    ALLEXCEPT(
        Sales,
        Sales[Category]
    )
)

«Remove every filter on the Sales table except the Category filter.»

14.4 ALLSELECTED

ALLSELECTED removes filters that come from inside the visual, but keeps filters that come from outside (slicers, page filters, etc.). Extremely useful for "percentage of selected total".

Sales % of Selected =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALLSELECTED(Sales[Region])
    )
)

14.5 KEEPFILTERS

Normally, a filter argument inside CALCULATE replaces any existing filter on the same column. KEEPFILTERS changes that behavior so the new filter is intersected with the existing filter.

North Sales Keep =
CALCULATE(
    [Total Sales],
    KEEPFILTERS(
        Sales[Region] = "North"
    )
)

15. VALUES and DISTINCT

15.1 VALUES

VALUES returns the distinct values of a column that are visible under the current filter context. It returns a single-column table.

Selected Region Count =
COUNTROWS(
    VALUES(Sales[Region])
)

15.2 DISTINCT

Unique Customers =
COUNTROWS(
    DISTINCT(Sales[Customer])
)

16. Context Transition

Context transition is one of the most confusing ideas for beginners. We will go slowly.

When you use CALCULATE (or a measure, which internally uses CALCULATE) inside a row context, CALCULATE converts the current row context into an equivalent filter context. This conversion is called context transition.

ROW CONTEXT
     ↓
CALCULATE (or measure call)
     ↓
CONTEXT TRANSITION
     ↓
FILTER CONTEXT
     ↓
MEASURE EVALUATION

Placing a measure inside an iterator (or using CALCULATE inside a calculated column) triggers context transition.

17. Function Comparison Tables

SUM vs SUMX

COUNT Family

18. Common Beginner Mistakes

19. DAX Debugging Process

1. Check the table — Does the table exist and contain the expected rows?
2. Check the column — Is the column name spelled correctly? Is the data type correct?
3. Check data type — Numbers stored as text will cause problems.
4. Check filter context — What slicers, page filters, and visual filters are active?
5. Check row context — Are you inside an iterator or calculated column?
6. Test the base measure — Does the simplest version (e.g. SUM) work correctly?
7. Test FILTER separately — Create a temporary measure that only returns COUNTROWS of the FILTER result.
8. Test CALCULATE step by step — Add one filter at a time.
9. Break complicated formulas into smaller measures — Name each intermediate step.

20. Practice Exercises

Level 1 — Beginner

1. Count the number of Sales IDs.
2. Count the number of customer entries (use COUNTA).
3. Calculate total sales.
4. Calculate total cost.
5. Calculate average sales amount.
6. Count the total number of rows in Sales.

Level 2 — Beginner+

1. North Sales
2. South Sales
3. Electronics Sales
4. Furniture Sales
5. North Order Count
6. Average Electronics Sales

Level 3 — Intermediate

1. Sales above ₹30,000
2. North high-value sales (Region = North AND SalesAmount > 30000)
3. Sales percentage of total (by Region)
4. Category percentage of total
5. Selected-region percentage (using ALLSELECTED)

Level 4 — Advanced

1. CALCULATE + FILTER + SUMX combination
2. CALCULATE + AVERAGEX
3. ALL + CALCULATE for % of total
4. ALLEXCEPT example
5. ALLSELECTED example
6. KEEPFILTERS example
7. VALUES + COUNTROWS
8. Nested CALCULATE

21. Answer Key (Selected)

Level 1

Order Count =
COUNT(Sales[SalesID])
-- Result: 10

Total Sales =
SUM(Sales[SalesAmount])
-- Result: 276000

Total Orders =
COUNTROWS(Sales)
-- Result: 10

Level 2

North Sales =
CALCULATE(
    [Total Sales],
    Sales[Region] = "North"
)

Electronics Sales =
CALCULATE(
    [Total Sales],
    Sales[Category] = "Electronics"
)

Level 3

Sales Above 30000 =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[SalesAmount] > 30000
    )
)

Sales % of Total =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALL(Sales[Region])
    )
)

22. Mini Project — Sales Performance Dashboard

Build a realistic Sales Performance Dashboard using the measures below. Create each measure step by step and place them in cards, tables, and charts.

Total Sales = SUM(Sales[SalesAmount])
Total Cost = SUM(Sales[Cost])
Total Profit = SUM(Sales[Profit])
Total Orders = COUNTROWS(Sales)
Average Order Value = AVERAGEX(Sales, Sales[SalesAmount])
North Sales = CALCULATE([Total Sales], Sales[Region] = "North")
South Sales = CALCULATE([Total Sales], Sales[Region] = "South")
Electronics Sales = CALCULATE([Total Sales], Sales[Category] = "Electronics")
Furniture Sales = CALCULATE([Total Sales], Sales[Category] = "Furniture")
Sales % of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Region])))
Category % of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Category])))
High Value Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[SalesAmount] > 30000))
Selected Region Sales = CALCULATE([Total Sales], ALLSELECTED(Sales[Region]))

23. DAX Cheat Sheet

24. Final DAX Learning Roadmap

You have now completed a full journey from absolute beginner to advanced DAX concepts.

What you should now be able to do:

• Explain the difference between a measure and a calculated column
• Describe filter context and row context in simple words
• Choose correctly between COUNT / COUNTA / COUNTX / COUNTROWS
• Decide when to use SUM vs SUMX and AVERAGE vs AVERAGEX
• Write CALCULATE measures with simple and multiple filters
• Use FILTER for complex conditions
• Build percentage-of-total measures with ALL / REMOVEFILTERS / ALLSELECTED
• Understand the purpose of ALLEXCEPT and KEEPFILTERS
• Debug a measure systematically
• Build a complete Sales Performance Dashboard

Congratulations on completing the DAX Mastery Student Handbook.
You now understand not only what formula to write, but why it works.