IF function for conditions in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to classify each sale as 'High Value' or 'Regular' based on the sale amount. Sales above $500 should be labeled 'High Value', others as 'Regular'.

📊 Data: You have a sales dataset with columns: Sale ID, Product, Sale Amount, and Date.

🎯 Deliverable: Create a new column in Power BI using the IF function to classify sales, then build a simple table visual showing Sale ID, Sale Amount, and the new classification.

Sample Data

Sale ID	Product	Sale Amount	Date
101	Headphones	450	2024-05-01
102	Smartphone	700	2024-05-02
103	Laptop	1200	2024-05-03
104	Charger	50	2024-05-04
105	Tablet	300	2024-05-05
106	Monitor	550	2024-05-06
107	Keyboard	80	2024-05-07
108	Mouse	40	2024-05-08

1

Step 1: Open Power BI Desktop and load the sales data table.

No formula needed for this step.

Expected Result

Sales data table is loaded and visible in the Fields pane.

2

Step 2: Create a new calculated column to classify sales using the IF function.

Sales Classification = IF('Sales'[Sale Amount] > 500, "High Value", "Regular")

Expected Result

A new column 'Sales Classification' is added with values 'High Value' for sales above 500 and 'Regular' otherwise.

3

Step 3: Create a table visual on the report canvas.

Add fields: Sale ID, Sale Amount, Sales Classification to the table visual.

Expected Result

Table shows each sale with its amount and classification.

4

Step 4: Format the table for readability: adjust column widths and enable word wrap if needed.

Use the Format pane to adjust visual settings.

Expected Result

Table is clear and easy to read.

Final Result

-----------------------------------------
| Sale ID | Sale Amount | Classification |
-----------------------------------------
| 101     | 450         | Regular       |
| 102     | 700         | High Value    |
| 103     | 1200        | High Value    |
| 104     | 50          | Regular       |
| 105     | 300         | Regular       |
| 106     | 550         | High Value    |
| 107     | 80          | Regular       |
| 108     | 40          | Regular       |
-----------------------------------------

✓Sales above $500 are correctly labeled as 'High Value'.

✓Most sales are 'Regular' with amounts $500 or less.

✓This classification helps quickly identify important sales.

Bonus Challenge

Create a measure that counts how many 'High Value' sales occurred.

Show Hint

Use the COUNTROWS and FILTER functions together, like: HighValueCount = COUNTROWS(FILTER('Sales', 'Sales'[Sale Amount] > 500))

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