Why intermediate DAX solves business questions in Power BI - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate total sales using DAX.

Power BI

Total Sales = SUM([1])

Drag options to blanks, or click blank then click option'

ASales[Amount]

BSales[Date]

CCustomers[Name]

DProducts[Category]

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-numeric column like date or name inside SUM.

The SUM function adds all values in the Sales[Amount] column to get total sales.

2fill in blank

medium

Complete the code to calculate average sales per customer.

Power BI

Average Sales = DIVIDE(SUM(Sales[Amount]), [1])

Drag options to blanks, or click blank then click option'

ASUM(Customers[CustomerID])

BDISTINCTCOUNT(Sales[CustomerID])

CCOUNT(Sales[Amount])

DCOUNTROWS(Products)

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT which counts all rows, not unique customers.

DISTINCTCOUNT counts unique customers to divide total sales by number of customers.

3fill in blank

hard

Fix the error in this DAX measure to calculate sales growth percentage.

Power BI

Sales Growth % = DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), [1]), CALCULATE(SUM(Sales[Amount]), [1]))

Drag options to blanks, or click blank then click option'

APREVIOUSYEAR(Sales[Date])

BNEXTYEAR(Sales[Date])

CPREVIOUSMONTH(Sales[Date])

DSAMEPERIODLASTYEAR(Sales[Date])

Attempts:

3 left

💡 Hint

Common Mistakes

Using PREVIOUSYEAR which returns the whole previous year, not the same period.

SAMEPERIODLASTYEAR returns the same period last year for correct year-over-year comparison.

4fill in blank

hard

Fill both blanks to create a measure that counts customers with sales over 1000.

Power BI

High Value Customers = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), FILTER(ALL(Sales), Sales[Amount] [1] [2]))

Drag options to blanks, or click blank then click option'

A>

B1000

C<

D500

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' or wrong threshold values.

The filter keeps sales where amount is greater than 1000 to count those customers.

5fill in blank

hard

Fill all three blanks to create a measure that calculates total sales for the current year.

Power BI

Total Sales CY = CALCULATE(SUM(Sales[Amount]), [1](ALL(Sales[Date]), [2](Sales[Date]) = YEAR([3]())))

Drag options to blanks, or click blank then click option'

AFILTER

BYEAR

CTODAY

DALL

Attempts:

3 left

💡 Hint

Common Mistakes

Using ALL which removes filters, or missing TODAY function.

Use FILTER to filter dates where YEAR equals the current year from TODAY function.

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