Why intermediate DAX solves business questions in Power BI - Formula Trace Breakdown

Sample Data

Sales and cost data for three products

Cell	Value
A1	Product
B1	Sales
C1	Cost
A2	Apples
B2	100
C2	60
A3	Bananas
B3	150
C3	90
A4	Cherries
B4	200
C4	120

Formula Trace

Profit = SUM(Sales) - SUM(Cost)

Step 1: SUM(Sales)

Step 2: SUM(Cost)

Step 3: SUM(Sales) - SUM(Cost)

Cell Reference Map

    A       B       C
1 Product  Sales   Cost
2 Apples    100     60
3 Bananas   150     90
4 Cherries  200    120

Sales values in B2:B4 and Cost values in C2:C4 are used in the formula.

The formula uses the Sales column (B2:B4) and Cost column (C2:C4) to calculate profit.

Result

    A       B       C       D
1 Product  Sales   Cost   Profit
2 Apples    100     60     
3 Bananas   150     90     
4 Cherries  200    120     
5                      180

Profit is shown as the total of sales minus total of cost in cell D5.

The final profit value 180 is displayed below the data, showing total profit from all products.

Sheet Trace Quiz - 3 Questions

What is the total sales value used in the formula?

A 450

B 270

C 180

D 100

Key Result

SUM(Column1) - SUM(Column2) calculates total difference between two columns.

Practice

1. What is the main reason intermediate DAX helps solve business questions better than basic DAX?

easy

A. It only works with small datasets.

B. It changes the calculation context to focus on specific data.

C. It uses simpler functions that are easier to understand.

D. It avoids using any filters or conditions.

Solution

1. Step 1: Understand calculation context in DAX

   Intermediate DAX uses functions like CALCULATE to change the context, focusing calculations on specific data subsets.

2. Step 2: Compare with basic DAX

   Basic DAX performs calculations in the default context without filtering or modifying data focus.

3. Final Answer:

   It changes the calculation context to focus on specific data. -> Option B

4. Quick Check:

   Calculation context = focus on data [OK]

Hint: Remember: CALCULATE changes context to answer specific questions [OK]

Common Mistakes:

• Thinking intermediate DAX is simpler than basic DAX
• Believing it only works on small data
• Ignoring the role of filters in calculations

2. Which of the following is the correct syntax to calculate total sales filtered by a specific product category using CALCULATE in DAX?

easy

A. SUM(Sales[Amount]) FILTER Sales[Category] = "Bikes"

B. CALCULATE(SUM(Sales[Amount]), Sales[Category] == "Bikes")

C. CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Bikes"))

D. CALCULATE(Sales[Amount], Sales[Category] == "Bikes")

Solution

1. Step 1: Understand CALCULATE with FILTER

   CALCULATE requires an expression and a filter table; FILTER creates a table filtered by condition.

2. Step 2: Check syntax correctness

   CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Bikes")) correctly uses CALCULATE with SUM and FILTER function with proper syntax.

3. Final Answer:

   CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Bikes")) -> Option C

4. Quick Check:

   CALCULATE + FILTER = correct syntax [OK]

Hint: Use FILTER inside CALCULATE for complex conditions [OK]

Common Mistakes:

• Using '==' instead of '=' in DAX
• Omitting FILTER function when needed
• Wrong order of arguments in CALCULATE

3. Given the measure:
Total Sales Bikes = CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Bikes")
What will be the result if the Sales table has:
- Bikes sales: 1000
- Accessories sales: 500
- Clothing sales: 300
?

medium

A. 1000

B. 0

C. 500

D. 1800

Solution

1. Step 1: Understand the filter applied

   The measure filters Sales to only include rows where Category = "Bikes".

2. Step 2: Sum the filtered sales

   Only Bikes sales (1000) are summed; other categories are excluded.

3. Final Answer:

   1000 -> Option A

4. Quick Check:

   Sum filtered by Bikes = 1000 [OK]

Hint: Filter limits sum to Bikes only [OK]

Common Mistakes:

• Summing all categories ignoring filter
• Confusing Accessories or Clothing sales as Bikes
• Assuming filter includes multiple categories

4. You wrote this DAX measure:
Sales Bikes = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] == "Bikes"))
But it returns an error. What is the problem?

medium

A. Using '==' instead of '=' for comparison in DAX.

B. SUM cannot be used inside CALCULATE.

C. FILTER cannot be used inside CALCULATE.

D. Missing quotation marks around Bikes.

Solution

1. Step 1: Check comparison operator in DAX

   DAX uses single '=' for equality, not '==' like some programming languages.

2. Step 2: Identify syntax error

   Using '==' causes syntax error; replacing with '=' fixes it.

3. Final Answer:

   Using '==' instead of '=' for comparison in DAX. -> Option A

4. Quick Check:

   Use '=' for equality in DAX [OK]

Hint: Use single '=' for comparisons in DAX [OK]

Common Mistakes:

• Confusing DAX syntax with other languages
• Thinking FILTER can't be inside CALCULATE
• Forgetting quotes around text values

5. You want to calculate the percentage growth of sales for the current year compared to the previous year using intermediate DAX. Which approach correctly uses CALCULATE and FILTER to achieve this?

hard

A. Growth % = SUM(Sales[Amount]) - SUM(Sales[Amount])

B. Growth % = SUM(Sales[Amount]) / SUM(Sales[Amount])

C. Growth % = CALCULATE(SUM(Sales[Amount]), Sales[Year] = MAX(Sales[Year]) + 1)

D. Growth % = DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = MAX(Sales[Year]) - 1)), CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = MAX(Sales[Year]) - 1)))

Solution

1. Step 1: Calculate current and previous year sales

   Use SUM(Sales[Amount]) for current year and CALCULATE with FILTER for previous year sales.

2. Step 2: Compute percentage growth

   Subtract previous year sales from current year sales, then divide by previous year sales using DIVIDE for safe division.

3. Final Answer:

   Growth % = DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = MAX(Sales[Year]) - 1)), CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = MAX(Sales[Year]) - 1))) -> Option D

4. Quick Check:

   Use CALCULATE + FILTER for previous year, DIVIDE for growth [OK]

Hint: Use CALCULATE with FILTER for previous year sales [OK]

Common Mistakes:

• Not filtering previous year correctly
• Dividing by zero without DIVIDE function
• Using wrong year comparison operators