Optimizing DAX queries in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales Using SUMX with FILTER

Given a Sales table with columns [Product], [Quantity], and [Price], which DAX measure correctly calculates the total sales only for products with Quantity greater than 10?

A Total Sales = SUMX(FILTER(Sales, Sales[Quantity] > 10), Sales[Quantity] * Sales[Price])

B Total Sales = SUMX(Sales, IF(Sales[Quantity] > 10, Sales[Quantity] * Sales[Price], 0))

C Total Sales = CALCULATE(SUMX(Sales, Sales[Quantity] * Sales[Price]), Sales[Quantity] > 10)

D Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])

💡 Hint

Use FILTER to limit rows before calculation.

Option A uses FILTER to select rows where Quantity > 10, then SUMX multiplies Quantity by Price for those rows. Option A is invalid because IF without ELSE returns blank for false, causing errors. Option A has incorrect syntax for CALCULATE filter. Option A sums all rows without filtering.

❓ visualization

intermediate

Best Visualization for Showing Sales Trend Over Time

You want to show how total sales change month by month over the last year. Which visualization type is best to clearly show this trend?

A Stacked Bar Chart

B Line Chart

C Pie Chart

D Scatter Plot

💡 Hint

Think about how to show changes over time clearly.

A line chart is best for showing trends over time because it connects data points in order, making it easy to see increases or decreases month to month. Stacked bar charts are better for comparing parts of a whole, pie charts show proportions at one time, and scatter plots show relationships between two variables.

❓ data_modeling

advanced

Optimizing Relationships for Faster DAX Queries

You have two large tables: Customers and Sales. You want to optimize DAX queries that calculate sales by customer region. Which relationship setup improves performance the most?

A Create no relationship and use LOOKUPVALUE in measures

B Create a many-to-many relationship between Customers and Sales on CustomerID with both directions filtering

C Create a one-to-one relationship between Customers and Sales on CustomerID with single direction filtering

D Create a one-to-many relationship from Customers to Sales on CustomerID with single direction filtering

💡 Hint

Think about how relationships filter data efficiently.

Option D is best because a one-to-many relationship with single direction filtering is efficient and common for Customers to Sales. Many-to-many with both directions (Option D) can slow queries. One-to-one (Option D) is rare and not suitable here. Using LOOKUPVALUE (Option D) is less efficient than relationships.

🔧 Formula Fix

advanced

Identify the Error in This DAX Measure

What error will this DAX measure cause?

Average Sales = AVERAGE(Sales[Quantity] * Sales[Price])

Power BI

Average Sales = AVERAGE(Sales[Quantity] * Sales[Price])

A TypeError: AVERAGE expects a single column, not an expression

B SyntaxError: Cannot multiply columns inside AVERAGE

C No error, returns average sales amount

D RuntimeError: Division by zero

💡 Hint

Check what AVERAGE function accepts as input.

AVERAGE expects a single column reference, not an expression like multiplication. Option A correctly identifies the type error. Option A is not a syntax error but a semantic one. Option A is incorrect because the measure will fail. Option A is unrelated.

🧠 Conceptual

expert

Why Use Variables in Complex DAX Measures?

What is the main benefit of using variables (VAR) inside complex DAX measures?

A Variables automatically create new columns in the data model

B Variables allow storing data outside the model for faster access

C Variables improve readability and avoid repeated calculations, boosting performance

D Variables replace the need for relationships between tables

💡 Hint

Think about how variables help inside a formula.

Variables let you store intermediate results to reuse them, making code easier to read and often faster by avoiding repeated calculations. They do not store data outside the model (B), create columns (C), or replace relationships (D).