Variables (VAR/RETURN) in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales Using Variables

Given the following DAX measure, what is the output if the Sales table has 3 rows with SalesAmount values 100, 200, and 300?

Measure = VAR Total = SUM(Sales[SalesAmount]) RETURN Total

Power BI

Measure = VAR Total = SUM(Sales[SalesAmount]) RETURN Total

A 100

B 300

C 600

D SUM(Sales[SalesAmount])

💡 Hint

Think about what SUM does over all rows and how the variable stores that result.

The variable Total stores the sum of all SalesAmount values (100 + 200 + 300 = 600). The RETURN statement outputs this value.

🎯 Scenario

intermediate

Using Variables to Improve Readability

You want to calculate the profit margin percentage as (Total Profit / Total Sales) * 100. Which DAX measure below correctly uses variables to calculate this?

A Profit Margin = VAR SalesTotal = SUM(Sales[SalesAmount]) RETURN SUM(Sales[Profit]) / SalesTotal * 100

B Profit Margin = SUM(Sales[Profit]) / SUM(Sales[SalesAmount]) * 100

C Profit Margin = VAR Profit = SUM(Sales[Profit]) RETURN Profit / SUM(Sales[SalesAmount]) * 100

D Profit Margin = VAR Profit = SUM(Sales[Profit]) VAR SalesTotal = SUM(Sales[SalesAmount]) RETURN DIVIDE(Profit, SalesTotal) * 100

💡 Hint

Using variables for both numerator and denominator improves clarity and performance.

Option D defines both Profit and SalesTotal as variables and then divides them, which is clear and efficient.

🔧 Formula Fix

advanced

Identify the Error in Variable Usage

What error will this DAX measure produce?

Measure = VAR Total = SUM(Sales[Amount]) RETURN Total + Total2

Power BI

Measure = VAR Total = SUM(Sales[Amount]) RETURN Total + Total2

A Error: The variable 'Total2' is not defined.

B Error: Syntax error near 'RETURN'.

C Returns double the sum of Sales[Amount].

D Returns the sum of Sales[Amount] without error.

💡 Hint

Check if all variables used in RETURN are declared.

Total2 is used but never declared, causing a variable not found error.

❓ visualization

advanced

Best Visualization for Comparing Variable Results

You created two variables in a measure: VAR Sales2023 and VAR Sales2024. You want to show their values side by side for each product category. Which visualization is best?

A Clustered Column Chart with Product Category on axis and two measures for Sales2023 and Sales2024

B Pie Chart showing combined Sales2023 and Sales2024 totals

C Line Chart with Product Category on axis and Sales2023 only

D Table showing only Sales2023 values

💡 Hint

You want to compare two values side by side across categories.

Clustered Column Chart allows side-by-side comparison of two measures per category.

🧠 Conceptual

expert

Understanding Variable Evaluation Context

Consider this DAX measure:

Measure = VAR TotalSales = SUM(Sales[Amount]) RETURN TotalSales + CALCULATE(SUM(Sales[Amount]), ALL(Sales))

What does this measure calculate?

Power BI

Measure = VAR TotalSales = SUM(Sales[Amount]) RETURN TotalSales + CALCULATE(SUM(Sales[Amount]), ALL(Sales))

A Only total Sales ignoring filters

B Sum of Sales in current filter context plus total Sales ignoring filters

C Sum of Sales in current filter context multiplied by total Sales ignoring filters

D Sum of Sales in current filter context only

💡 Hint

ALL removes filters, so CALCULATE returns total sales ignoring filters.

TotalSales stores sum in current filters; CALCULATE with ALL returns total ignoring filters; adding both sums them.