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

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales for Each Region Using Intermediate DAX

You have a sales table with columns: Region, SalesAmount. You want to calculate total sales per region using an intermediate DAX measure with SUMX and VALUES.

Which DAX measure produces the correct total sales per region?

A Total Sales = SUMX(VALUES(Sales[Region]), CALCULATE(SUM(Sales[SalesAmount])))

B Total Sales = SUM(Sales[SalesAmount])

C Total Sales = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Region]))

D Total Sales = SUMX(Sales, Sales[SalesAmount])

💡 Hint

Think about how VALUES returns unique regions and SUMX iterates over them.

Option A uses VALUES to get unique regions and SUMX to sum sales per region correctly. Option A sums all sales ignoring regions. Option A removes region filter, summing all sales. Option A sums sales row by row without grouping.

🎯 Scenario

intermediate

Using Intermediate DAX to Calculate Year-Over-Year Growth

You have a sales table with Year and SalesAmount. You want to calculate the year-over-year (YoY) growth percentage using intermediate DAX.

Which DAX measure correctly calculates YoY growth?

A YoY Growth = SUM(Sales[SalesAmount]) / CALCULATE(SUM(Sales[SalesAmount]), NEXTYEAR(Calendar[Date]))

B YoY Growth = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSYEAR(Calendar[Date])) / SUM(Sales[SalesAmount])

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

D YoY Growth = SUM(Sales[SalesAmount]) - SUM(Sales[SalesAmount])

💡 Hint

Use SAMEPERIODLASTYEAR to get last year's sales for comparison.

Option C correctly calculates the difference between current and last year sales, then divides by last year sales to get growth percentage. Option C subtracts the same value, always zero. Option C divides last year sales by current, reversed. Option C uses NEXTYEAR which is incorrect for YoY.

🔧 Formula Fix

advanced

Identify the Error in This Intermediate DAX Measure

Consider this DAX measure intended to calculate average sales per customer:

Avg Sales per Customer = AVERAGEX(Customer, SUM(Sales[SalesAmount]))

What is the error in this measure?

A The table 'Customer' is not specified correctly; it should be 'Customers' or a valid table name.

B SUM(Sales[SalesAmount]) cannot be used inside AVERAGEX because it returns a scalar, not a row context value.

C AVERAGEX requires a table expression, but 'Customer' is a column, not a table.

D The measure is correct and will return average sales per customer.

💡 Hint

Check if the table name used in AVERAGEX exists and is spelled correctly.

Option A is correct because 'Customer' is likely a typo or invalid table name. The table should be 'Customers' or the actual customer table name. Option A is incorrect because SUM(Sales[SalesAmount]) inside AVERAGEX is valid when iterating over customers. Option A is wrong because 'Customer' is used as a table, not a column. Option A is incorrect due to the table name error.

❓ visualization

advanced

Choose the Best Visualization for Showing Sales Trends by Product Category

You want to show how sales change over time for different product categories. Which visualization type best communicates this information clearly and accessibly?

A Pie chart showing total sales by product category for the latest year.

B Stacked area chart with time on the x-axis and sales on the y-axis, colored by product category.

C Table listing sales numbers by product category and year.

D Scatter plot with sales on x-axis and product category on y-axis.

💡 Hint

Think about how to show trends over time and compare categories.

Option B is best because a stacked area chart shows sales trends over time and how each category contributes. Option B only shows one year, losing trend info. Option B is less visual and harder to interpret trends. Option B is not suitable for time series data.

🧠 Conceptual

expert

Why Use Intermediate DAX Measures Instead of Calculated Columns for Business Questions?

Which reason best explains why intermediate DAX measures are preferred over calculated columns when solving complex business questions in Power BI?

A Measures store data physically in the model, making them easier to manage.

B Calculated columns are faster to compute and always preferred for performance.

C Calculated columns can be used in visuals but measures cannot.

D Measures are calculated dynamically based on filters and context, providing flexible and efficient analysis.

💡 Hint

Think about how measures respond to user interactions and filters.

Option D is correct because measures calculate results on the fly considering filters and slicers, enabling dynamic insights. Option D is wrong; calculated columns increase model size and are less flexible. Option D is incorrect; calculated columns store data physically, measures do not. Option D is false; both can be used in visuals but measures are more flexible.