IF function for conditions in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Evaluate IF function with nested conditions

Given the DAX measure below, what is the output when the SalesAmount is 1500?

Result = IF(SalesAmount > 1000, "High", IF(SalesAmount > 500, "Medium", "Low"))

Power BI

Result = IF(SalesAmount > 1000, "High", IF(SalesAmount > 500, "Medium", "Low"))

A "High"

B "Medium"

C "Low"

D Blank

💡 Hint

Check the first condition and see if it is true for 1500.

Since 1500 is greater than 1000, the first IF condition is true, so the output is "High".

❓ visualization

intermediate

Choose the correct visualization for IF condition results

You created a measure using IF to categorize sales as "High", "Medium", or "Low". Which visualization best shows the count of sales in each category?

A Scatter plot of sales amount vs. date

B Line chart showing sales over time

C Table with raw sales data

D Pie chart showing percentage of each category

💡 Hint

Think about showing parts of a whole for categories.

A pie chart clearly shows the proportion of each sales category, making it easy to compare counts visually.

🧠 Conceptual

advanced

Understanding IF function short-circuit behavior

In Power BI DAX, when using nested IF functions, which statement is true about evaluation?

A All IF conditions are evaluated regardless of earlier results

B Only the first TRUE condition's result is returned, later conditions are not evaluated

C IF functions always return numeric values

D Nested IFs cannot be used inside measures

💡 Hint

Think about efficiency and how IF works in programming.

DAX IF functions stop evaluating once a TRUE condition is found, returning that result immediately.

🔧 Formula Fix

advanced

Identify the error in this IF function

What error will this DAX measure produce?

CheckValue = IF(SalesAmount > 1000, "High", "Medium", "Low")

A SyntaxError: Too many arguments in IF function

B Returns "High" for SalesAmount > 1000, else "Medium"

C No error, returns correct result

D Returns "Low" for all SalesAmount values

💡 Hint

Check the number of arguments IF accepts.

The IF function accepts only 3 arguments: condition, value if true, value if false. This has 4 arguments, causing a syntax error.

🎯 Scenario

expert

Create a measure to classify sales with multiple conditions

You want to create a DAX measure that classifies sales as:
- "Excellent" if SalesAmount > 2000
- "Good" if SalesAmount > 1000 and <= 2000
- "Average" if SalesAmount > 500 and <= 1000
- "Poor" if SalesAmount <= 500

Which of the following DAX expressions correctly implements this?

A SalesCategory = IF(SalesAmount > 2000, "Excellent", IF(SalesAmount > 1000 && SalesAmount <= 2000, "Good", IF(SalesAmount > 500 && SalesAmount <= 1000, "Average", "Poor")))

B SalesCategory = IF(SalesAmount >= 2000, "Excellent", IF(SalesAmount > 1000, "Good", IF(SalesAmount > 500, "Average", "Poor")))

C SalesCategory = IF(SalesAmount > 2000, "Excellent", IF(SalesAmount > 1000, "Good", IF(SalesAmount > 500, "Average", "Poor")))

D SalesCategory = IF(SalesAmount > 2000, "Excellent", IF(SalesAmount >= 1000, "Good", IF(SalesAmount >= 500, "Average", "Poor")))

💡 Hint

Remember that nested IFs check conditions in order, so use > and rely on order to cover ranges.

Option C correctly uses nested IFs with > comparisons and order to cover all ranges without overlap or gaps.

Practice

1. What does the IF function do in Power BI?

easy

A. Checks a condition and returns one value if true, another if false

B. Calculates the sum of a column

C. Filters rows based on a condition

D. Joins two tables together

Solution

1. Step 1: Understand the purpose of IF

   The IF function evaluates a condition and chooses between two results based on whether the condition is true or false.

2. Step 2: Compare with other options

   Calculating the sum of a column, filtering rows based on a condition, and joining tables describe other functions or operations, not IF.

3. Final Answer:

   Checks a condition and returns one value if true, another if false -> Option A

4. Quick Check:

   IF function = condition check [OK]

Hint: IF returns true-result or false-result based on condition [OK]

Common Mistakes:

• Confusing IF with SUM or FILTER functions
• Thinking IF joins tables
• Assuming IF only returns true values

2. Which of the following is the correct syntax for the IF function in DAX?

easy

A. IF condition then true_result else false_result

B. IF(condition; true_result; false_result)

C. IF{condition, true_result, false_result}

D. IF(condition, true_result, false_result)

Solution

1. Step 1: Recall DAX IF syntax

   The correct syntax uses parentheses and commas: IF(condition, true_result, false_result).

2. Step 2: Identify incorrect syntax

   Using 'then else' keywords, semicolons instead of commas, or braces are invalid in standard DAX syntax.

3. Final Answer:

   IF(condition, true_result, false_result) -> Option D

4. Quick Check:

   DAX IF syntax = parentheses and commas [OK]

Hint: Use parentheses and commas for IF in DAX [OK]

Common Mistakes:

• Using semicolons instead of commas
• Writing IF like a programming language if-then-else
• Using braces {} instead of parentheses

3. Given the DAX measure:

Sales Category = IF(SUM(Sales[Amount]) > 1000, "High", "Low")

What will be the result if the total sales amount is 750?

medium

A. 750

B. "High"

C. "Low"

D. Error

Solution

1. Step 1: Evaluate the condition

   The condition checks if total sales (750) is greater than 1000. 750 > 1000 is false.

2. Step 2: Determine the IF result

   Since the condition is false, the IF function returns the false_result, which is "Low".

3. Final Answer:

   "Low" -> Option C

4. Quick Check:

   750 > 1000 is false, so result = "Low" [OK]

Hint: Check condition result true/false to pick IF output [OK]

Common Mistakes:

• Assuming 750 is greater than 1000
• Returning the numeric value instead of text
• Confusing true and false results

4. Identify the error in this DAX formula:

Discount Status = IF SUM(Sales[Amount]) > 500, "Discount", "No Discount")

medium

A. No error, formula is correct

B. Missing parentheses around the IF arguments

C. Incorrect column name Sales[Amount]

D. Using double quotes instead of single quotes

Solution

1. Step 1: Check IF function syntax

   The IF function requires parentheses enclosing all arguments: IF(condition, true_result, false_result).

2. Step 2: Identify missing parentheses

   The formula lacks opening parentheses after IF and has an extra closing parenthesis at the end.

3. Final Answer:

   Missing parentheses around the IF arguments -> Option B

4. Quick Check:

   IF syntax needs parentheses [OK]

Hint: Always wrap IF arguments in parentheses [OK]

Common Mistakes:

• Forgetting parentheses after IF
• Confusing quotes usage (double quotes are correct here)
• Assuming column name is wrong without checking

5. You want to create a new column that labels sales as "High", "Medium", or "Low" based on Sales[Amount]:
- "High" if amount > 1000
- "Medium" if amount between 500 and 1000 (inclusive)
- "Low" if amount < 500

Which DAX formula correctly uses nested IF functions to achieve this?

hard

A. Sales Level = IF(Sales[Amount] > 1000, "High", IF(Sales[Amount] >= 500 && Sales[Amount] <= 1000, "Medium", "Low"))

B. Sales Level = IF(Sales[Amount] > 1000, "High", IF(Sales[Amount] > 500 && Sales[Amount] < 1000, "Medium", "Low"))

C. Sales Level = IF(Sales[Amount] > 1000, "High", IF(Sales[Amount] >= 500 || Sales[Amount] <= 1000, "Medium", "Low"))

D. Sales Level = IF(Sales[Amount] > 1000, "High", IF(Sales[Amount] > 500 || Sales[Amount] < 1000, "Medium", "Low"))

Solution

1. Step 1: Understand the conditions

   "High" if >1000, "Medium" if between 500 and 1000 inclusive, "Low" if less than 500.

2. Step 2: Check nested IF logic and operators

   Sales Level = IF(Sales[Amount] > 1000, "High", IF(Sales[Amount] >= 500 && Sales[Amount] <= 1000, "Medium", "Low")) correctly uses AND (&&) with >= and <= for the medium range. The other options have incorrect operators or use OR (||) which breaks the logic.

3. Final Answer:

   Sales Level = IF(Sales[Amount] > 1000, "High", IF(Sales[Amount] >= 500 && Sales[Amount] <= 1000, "Medium", "Low")) -> Option A

4. Quick Check:

   Nested IF with correct AND and inclusive bounds [OK]

Hint: Use nested IF with && and inclusive operators for ranges [OK]

Common Mistakes:

• Using OR (||) instead of AND (&&) for range checks
• Not including equal signs for inclusive ranges
• Incorrect logical operators causing wrong labels