Why intermediate DAX solves business questions in Power BI - Quick Recap

Recall & Review

beginner

What is DAX in Power BI?

DAX stands for Data Analysis Expressions. It is a formula language used in Power BI to create custom calculations and measures that help analyze data.

intermediate

Why do intermediate DAX skills help solve business questions?

Intermediate DAX skills let you create more complex calculations like time intelligence, filtering, and conditional logic, which help answer detailed business questions clearly.

beginner

What is a calculated measure in Power BI?

A calculated measure is a DAX formula that calculates a value dynamically based on the data context, like total sales or average profit, helping to answer specific business questions.

intermediate

How does filtering in DAX help in business analysis?

Filtering lets you focus on specific parts of your data, like sales in a region or a time period, so you can answer precise business questions without distractions.

intermediate

What is time intelligence in DAX?

Time intelligence functions in DAX help analyze data over time, like comparing sales this year to last year, which is important for understanding trends and making decisions.

Which DAX feature helps you calculate sales for the previous year?

A Data import

B Calculated columns

C Visual formatting

D Time intelligence functions

Time intelligence functions in DAX allow you to compare data across different time periods, such as previous year sales.

What does filtering in DAX allow you to do?

A Import data from Excel

B Change the color of charts

C Focus calculations on specific data subsets

D Create new tables

Filtering lets you narrow down data to specific parts, so calculations answer precise business questions.

Why are calculated measures important in Power BI?

A They create dynamic calculations based on data context

B They store raw data

C They format reports

D They import data

Calculated measures perform calculations dynamically depending on filters and context in your report.

Which skill level of DAX is typically needed to answer detailed business questions?

A Beginner

B Intermediate

C Expert

D No DAX needed

Intermediate DAX skills allow you to write more complex formulas needed for detailed business insights.

What kind of business questions can intermediate DAX help answer?

A Complex trends and comparisons over time

B Simple data entry

C Changing report colors

D Data import errors

Intermediate DAX helps analyze trends, comparisons, and filtered data to answer complex business questions.

Explain how intermediate DAX functions help solve business questions in Power BI.

Think about how formulas can change results based on what you want to see.

Describe a real-life example where intermediate DAX could help a business make a decision.

Imagine you are a store manager checking if sales improved this year.

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