Why advanced DAX handles complex scenarios in Power BI - Challenge Your Understanding

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales for Each Product Category Using Advanced DAX

Given a sales table with columns ProductCategory and SalesAmount, which DAX measure correctly calculates the total sales per product category ignoring any filters on ProductCategory?

A Total Sales = SUM(Sales[SalesAmount])

B Total Sales = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[ProductCategory]))

C Total Sales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[ProductCategory] = "Electronics"))

D Total Sales = CALCULATE(SUM(Sales[SalesAmount]), VALUES(Sales[ProductCategory]))

💡 Hint

Think about how to remove filters on ProductCategory to get total sales across all categories.

Option B uses ALL to remove filters on ProductCategory, so it sums sales for all categories regardless of current filters.

Option B respects current filters, so it does not ignore ProductCategory filters.

Option B filters only Electronics category, not all categories.

Option B keeps current filter context, so it does not ignore filters.

❓ visualization

intermediate

Choose the Best Visualization for Showing Sales Trends Over Time

You want to show how sales change month by month for the last year. Which visualization type best shows this trend clearly and simply?

A Line Chart

B Stacked Bar Chart

C Pie Chart

D Scatter Plot

💡 Hint

Think about which chart type shows changes over time best.

Line charts are best for showing trends over time because they connect data points in order.

Stacked bar charts are better for comparing parts of a whole.

Pie charts show proportions at one point in time, not trends.

Scatter plots show relationships between two variables, not trends over time.

❓ data_modeling

advanced

Identify the Correct Relationship Type for a Star Schema

In a star schema data model, what is the correct relationship type between the fact table and dimension tables?

A One-to-One

B Many-to-One

C Many-to-Many

D One-to-Many

💡 Hint

Consider how many records in the fact table relate to one record in a dimension table.

In a star schema, each dimension record relates to many fact records, so the relationship is One-to-Many from dimension to fact.

Many-to-Many is more complex and not typical in star schemas.

One-to-One is rare and not typical for fact-dimension relationships.

Many-to-One is the reverse direction and less common to define.

🎯 Scenario

advanced

Handling Missing Data in Sales Reporting

Your sales data has missing values for some dates. You want to show a continuous sales trend line without gaps. What is the best approach to handle this in Power BI?

A Replace missing sales values with blanks and use a clustered bar chart

B Filter out all dates with missing sales data

C Use a disconnected date table and create a measure that returns zero for missing dates

D Ignore missing data and use a pie chart to show sales distribution

💡 Hint

Think about how to keep the timeline continuous even if sales data is missing.

Using a disconnected date table with a measure that returns zero for missing dates ensures the line chart shows a continuous timeline without gaps.

Filtering out missing dates breaks the timeline.

Replacing missing values with blanks causes gaps in line charts.

Pie charts do not show trends over time.

🔧 Formula Fix

expert

Find the Error in This Complex DAX Measure

What error will this DAX measure cause?

Sales Growth = DIVIDE(SUM(Sales[SalesAmount]) - CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSYEAR(Calendar[Date])), CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSYEAR(Calendar[Date])))

Power BI

Sales Growth = DIVIDE(SUM(Sales[SalesAmount]) - CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSYEAR(Calendar[Date])), CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSYEAR(Calendar[Date])))

A Runtime error because PREVIOUSYEAR requires a continuous date column in the Calendar table

B Returns blank because DIVIDE function is missing the alternate result parameter

C Returns incorrect result because SUM is used instead of SUMX

D SyntaxError due to missing parentheses

💡 Hint

Check the requirements of the PREVIOUSYEAR function and the Calendar table.

PREVIOUSYEAR requires a continuous date column in the Calendar table. If the Calendar table is missing dates or is not marked as a date table, it causes a runtime error.

There is no syntax error in the measure.

DIVIDE without alternate result returns blank on divide by zero but does not cause error.

SUM is valid here because SalesAmount is a column; SUMX is not required.