Working with IF, SWITCH, AND, and OR in Power BI

Course: Microsoft Power BI Training

Topic: Working with IF, SWITCH, AND, and OR

Course Link: Microsoft Power BI Training – JustAcademy

Demo Link: Register for a Free Course Demo

Learning Resource: Microsoft Learn – DAX Documentation

Website: JustAcademy

1. Introduction to Conditional Logic in Power BI

Conditional logic allows a Power BI report to make decisions based on data values and business rules. It helps classify records, create categories, evaluate performance, apply eligibility rules, and display meaningful results instead of raw values.

In Power BI, conditional expressions are commonly written using Data Analysis Expressions (DAX). Four important functions and operators for building conditional logic are IF, SWITCH, AND, and OR.

For example, a sales report may classify revenue as "High" or "Low", assign a performance rating to each salesperson, identify orders that meet multiple requirements, or flag a transaction when at least one warning condition is true.

1.1 What Is DAX?

Data Analysis Expressions (DAX) is a formula language used in Power BI, Power Pivot, and Analysis Services to create calculations. DAX supports functions, operators, variables, and expressions that evaluate data according to the current filter and row context.

DAX is used to create:

• Calculated columns: Calculations evaluated for each row and stored in the model.
• Measures: Calculations evaluated dynamically according to report filters, slicers, and visual context.
• Calculated tables: Tables generated from DAX expressions.

1.2 What Is Conditional Logic?

Conditional logic evaluates a condition and returns a result depending on whether that condition is true or false. Conditions often use comparison operators such as =, >, <, >=, and <=.

Example:

Sales Category =
IF(
    Sales[SalesAmount] >= 10000,
    "High Sales",
    "Low Sales"
)

This calculated column checks each row. If the sales amount is at least 10,000, it returns "High Sales"; otherwise, it returns "Low Sales".

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand conditional expressions and their role in Power BI reports.
• Use the IF function to evaluate true and false conditions.
• Build multiple-condition classifications with nested IF statements.
• Use SWITCH to evaluate an expression or test multiple conditions.
• Combine conditions using the AND and OR functions.
• Use the logical operators && and || as alternatives to AND and OR functions.
• Combine IF, SWITCH, AND, and OR to implement business rules.
• Choose between calculated columns and measures for conditional calculations.
• Debug common DAX errors and improve formula readability.
• Apply conditional logic to sales, profit, customer, and performance reports.

3. Understanding the IF Function

The IF function checks a logical condition and returns one result when the condition is true and another when it is false. It is one of the most frequently used DAX functions for classification and decision-making.

3.1 Syntax of IF

IF(<logical_test>, <value_if_true>[, <value_if_false>])

If the third argument is omitted, DAX returns FALSE when the logical test is false. In many reporting situations, it is clearer to provide an explicit false result.

3.2 Simple IF Example

Suppose a table named Sales contains a column named SalesAmount. The business wants to label sales of 10,000 or more as high sales.

Sales Category =
IF(
    Sales[SalesAmount] >= 10000,
    "High Sales",
    "Low Sales"
)

Possible results:

3.3 IF with Numeric Results

IF can return numbers as well as text. For example, a company may award a bonus of 500 when a salesperson meets a target.

Sales Bonus =
IF(
    Sales[SalesAmount] >= 20000,
    500,
    0
)

This formula returns 500 for qualifying rows and 0 for other rows. Numeric results are useful for bonus calculations, flags, and conditional amounts.

3.4 IF with a Blank Result

Sometimes, a report should show a result only when a condition is met. Use BLANK() when the false result should be empty rather than zero or a text label.

Qualified Sales =
IF(
    Sales[SalesAmount] >= 10000,
    Sales[SalesAmount],
    BLANK()
)

Blank values can affect how data appears in visuals, so use them intentionally. A blank is not the same as zero.

3.5 Nested IF Statements

A nested IF places one IF function inside another. This is useful when a business rule has several possible outcomes.

Sales Performance =
IF(
    Sales[SalesAmount] >= 30000,
    "Excellent",
    IF(
        Sales[SalesAmount] >= 20000,
        "Good",
        IF(
            Sales[SalesAmount] >= 10000,
            "Average",
            "Needs Improvement"
        )
    )
)

The formula evaluates conditions from the outside inward. When the first condition is true, its result is returned. Otherwise, the next IF is evaluated.

Important: Order nested IF conditions carefully. In the example above, the highest threshold is checked first. If a lower threshold is checked first, a value may match it before the more specific condition is reached.

3.6 When to Use IF

• When there are two primary outcomes, such as pass/fail or eligible/not eligible.
• When a calculation depends on a threshold.
• When assigning a flag based on a condition.
• When returning different numeric values for different situations.
• When the formula has only a small number of outcomes.

4. Understanding the SWITCH Function

The SWITCH function evaluates an expression against a list of values and returns the result associated with the first matching value. It can make formulas easier to read than a long chain of nested IF statements when comparing one expression against several specific values.

4.1 Syntax of SWITCH

SWITCH(
    <expression>,
    <value>, <result>,
    [ <value>, <result> ... ],
    [ <else> ]
)

4.2 SWITCH with Categories

Suppose a table contains a column called OrderStatus. A report needs readable descriptions for common status codes.

Order Status Description =
SWITCH(
    Sales[OrderStatus],
    "P", "Pending",
    "S", "Shipped",
    "D", "Delivered",
    "C", "Cancelled",
    "Unknown Status"
)

If the status is "S", the result is "Shipped". If none of the listed codes matches, the formula returns "Unknown Status". Including a default result makes unexpected values easier to identify.

4.3 SWITCH for Numeric Classification

SWITCH can also classify numeric codes or values that already represent a defined category.

Customer Tier =
SWITCH(
    Customers[TierCode],
    1, "Bronze",
    2, "Silver",
    3, "Gold",
    4, "Platinum",
    "Unassigned"
)

This approach is appropriate when each tier code has a known meaning. It is not the best choice when the decision depends on numeric ranges, such as sales being greater than or equal to a threshold.

4.4 SWITCH(TRUE()) for Multiple Conditions

A useful DAX pattern is SWITCH(TRUE(), ...). In this pattern, each condition is evaluated in order. The result associated with the first true condition is returned. This is particularly useful for range-based classifications.

Sales Performance =
SWITCH(
    TRUE(),
    Sales[SalesAmount] >= 30000, "Excellent",
    Sales[SalesAmount] >= 20000, "Good",
    Sales[SalesAmount] >= 10000, "Average",
    "Needs Improvement"
)

This formula is similar to the nested IF example but is often easier to scan and maintain. Check the most restrictive or highest threshold first because the first true condition wins.

4.5 SWITCH with a Default Result

Region Group =
SWITCH(
    Sales[Region],
    "North", "Northern Region",
    "South", "Southern Region",
    "East", "Eastern Region",
    "West", "Western Region",
    "Other Region"
)

The final argument is the default result. If the region contains an unexpected value, the formula returns "Other Region". Without a default result, SWITCH returns BLANK when there is no match.

4.6 SWITCH Compared with Nested IF

4.7 When to Use SWITCH

• When mapping codes to labels.
• When translating status values into descriptions.
• When assigning categories based on several known values.
• When using SWITCH(TRUE()) to simplify multiple ordered conditions.
• When a formula needs a clearly defined default result.

5. Understanding the AND Function

The AND function checks whether all supplied logical conditions are true. It returns TRUE only when every condition is true. If any condition is false, the result is FALSE.

5.1 Syntax of AND

AND(<logical1>, <logical2>)

The AND function accepts two logical arguments. For combining more than two conditions, DAX also supports the && logical operator, which can be chained across multiple conditions.

5.2 Simple AND Example

Suppose a sales transaction qualifies for a reward only when the sales amount is at least 10,000 and the profit is at least 2,000.

Reward Eligibility =
IF(
    AND(
        Sales[SalesAmount] >= 10000,
        Sales[Profit] >= 2000
    ),
    "Eligible",
    "Not Eligible"
)

The reward is assigned only if both conditions are true.

5.3 Using the AND Operator (&&)

The logical operator && can be used instead of the AND function. Many DAX developers find it convenient when writing several conditions.

Reward Eligibility =
IF(
    Sales[SalesAmount] >= 10000
        && Sales[Profit] >= 2000,
    "Eligible",
    "Not Eligible"
)

The formula has the same intended logic as the previous example. Use parentheses to make complicated combinations of conditions easier to read.

5.4 AND with Three or More Conditions

Imagine a customer qualifies for a loyalty benefit only if the customer is active, has made at least five purchases, and has spent at least 20,000.

Customer Benefit Status =
IF(
    Customers[IsActive] = TRUE()
        && Customers[PurchaseCount] >= 5
        && Customers[TotalSpend] >= 20000,
    "Qualified",
    "Not Qualified"
)

Every requirement must be met. If even one condition is false, the result is "Not Qualified". Ensure the data types match the expressions; for example, use a true/false field for logical status where possible rather than inconsistent text labels.

5.5 Common Business Uses of AND

• Checking whether sales and profit targets are both achieved.
• Determining whether an order meets several shipping requirements.
• Identifying customers who meet activity and spending criteria.
• Checking whether a date falls within a required period and another rule is met.
• Flagging transactions that satisfy all compliance conditions.

6. Understanding the OR Function

The OR function checks whether at least one of its logical conditions is true. It returns TRUE when one or more conditions are true and returns FALSE only when all conditions are false.

6.1 Syntax of OR

OR(<logical1>, <logical2>)

OR takes two logical arguments. To combine multiple conditions, use the || operator. This operator can be chained to evaluate several alternative conditions.

6.2 Simple OR Example

A sales representative receives recognition if sales are at least 25,000 or profit is at least 5,000.

Recognition Status =
IF(
    OR(
        Sales[SalesAmount] >= 25000,
        Sales[Profit] >= 5000
    ),
    "Recognized",
    "Not Recognized"
)

Only one of the two conditions needs to be true for the representative to qualify.

6.3 Using the OR Operator (||)

Recognition Status =
IF(
    Sales[SalesAmount] >= 25000
        || Sales[Profit] >= 5000,
    "Recognized",
    "Not Recognized"
)

The || operator is an alternative to the OR function. It is especially convenient when several alternative conditions must be combined.

6.4 OR with Multiple Conditions

A support team may flag an order if it is delayed, cancelled, or marked as high priority.

Order Attention Flag =
IF(
    Sales[OrderStatus] = "Delayed"
        || Sales[OrderStatus] = "Cancelled"
        || Sales[Priority] = "High",
    "Review Required",
    "Normal"
)

The formula returns "Review Required" if any of the three conditions is true. It returns "Normal" only when all three are false.

6.5 Common Business Uses of OR

• Flagging orders that are delayed or cancelled.
• Recognizing employees who meet at least one performance target.
• Identifying customers who qualify through different eligibility routes.
• Finding transactions with one or more warning indicators.
• Creating alerts when any of several risk conditions is present.

7. AND vs OR: Key Differences

Remember: AND means all conditions must be true. OR means at least one condition must be true.

7.1 Choosing the Right Logic

• Use AND when every requirement must be satisfied.
• Use OR when there are alternative ways to qualify.
• Use IF when a true/false condition should return different results.
• Use SWITCH when matching one expression to multiple values.
• Use SWITCH(TRUE()) when evaluating several ordered logical conditions.

8. Combining IF with AND and OR

In practical reports, IF often works together with AND or OR. AND or OR produces a logical result, and IF converts that result into a label, number, or other output.

8.1 IF with AND

Example: A salesperson receives a performance bonus only when both sales and profit targets are achieved.

Performance Bonus =
IF(
    Sales[SalesAmount] >= 30000
        && Sales[Profit] >= 5000,
    1000,
    0
)

This formula returns a bonus of 1,000 only when both thresholds are met. Otherwise, it returns 0.

8.2 IF with OR

Example: An order is flagged if either its delivery status is delayed or its value is greater than 50,000.

Order Alert =
IF(
    Sales[OrderStatus] = "Delayed"
        || Sales[SalesAmount] > 50000,
    "Attention Required",
    "No Alert"
)

The alert is triggered when either condition is true.

8.3 Combining AND and OR

Suppose a company wants to flag a transaction when it is high-value and low-profit, or when it is cancelled. The intended logic is:

• The sales amount is greater than 50,000 and profit is below 2,000; or
• The order status is "Cancelled".

Transaction Review =
IF(
    (
        Sales[SalesAmount] > 50000
            && Sales[Profit] < 2000
    )
        || Sales[OrderStatus] = "Cancelled",
    "Review",
    "Normal"
)

Parentheses clearly group the high-value and low-profit requirements together. The outer OR then checks whether that combined condition is true or the order is cancelled.

8.4 Understanding Parentheses and Evaluation

When a formula combines AND and OR, parentheses help communicate the intended business rule and avoid ambiguity. Do not rely on memory or guesswork when translating complex rules; write the rule in plain language first, then represent each group explicitly in DAX.

IF(
    (ConditionA && ConditionB)
        || (ConditionC && ConditionD),
    "Qualified",
    "Not Qualified"
)

This structure means that both A and B must be true, or both C and D must be true, for the result to be "Qualified".

9. Combining SWITCH with AND and OR

SWITCH(TRUE()) can be used to evaluate several business rules in order. Each result may depend on AND, OR, or a combination of conditions.

9.1 SWITCH(TRUE()) with AND

Example: Classify sales performance using both revenue and profit.

Performance Category =
SWITCH(
    TRUE(),
    Sales[SalesAmount] >= 30000
        && Sales[Profit] >= 5000, "Excellent",
    Sales[SalesAmount] >= 20000
        && Sales[Profit] >= 3000, "Good",
    Sales[SalesAmount] >= 10000, "Average",
    "Needs Improvement"
)

The formula checks the conditions in order. If a record meets the first condition, "Excellent" is returned. If not, the second condition is checked, followed by the remaining conditions.

9.2 SWITCH(TRUE()) with OR

Example: Assign an order attention category when the order is delayed or cancelled.

Order Priority =
SWITCH(
    TRUE(),
    Sales[OrderStatus] = "Cancelled"
        || Sales[OrderStatus] = "Delayed", "Urgent",
    Sales[Priority] = "High", "High Priority",
    Sales[Priority] = "Normal", "Normal Priority",
    "Unclassified"
)

If an order is both delayed and high priority, the first matching condition determines the result. Here, it is classified as "Urgent". Change the order of conditions if the business requires a different priority hierarchy.

9.3 Multiple Rules with SWITCH(TRUE())

Customer Segment =
SWITCH(
    TRUE(),
    Customers[TotalSpend] >= 50000
        && Customers[PurchaseCount] >= 10, "VIP",
    Customers[TotalSpend] >= 20000
        || Customers[PurchaseCount] >= 5, "Regular",
    Customers[PurchaseCount] > 0, "New or Occasional",
    "No Purchases"
)

This formula combines conditions to assign customer segments. The VIP rule is evaluated first. The Regular rule is checked next, followed by the remaining rules. Validate the rule order against the intended customer segmentation policy.

10. Using Conditional Logic in Calculated Columns

A calculated column evaluates a DAX expression for each row and stores the resulting value in the data model. Conditional columns are helpful when a row-level label must be reused in tables, slicers, filters, or category axes.

10.1 Example: Sales Status Column

Sales Status =
IF(
    Sales[SalesAmount] >= 10000,
    "Target Met",
    "Below Target"
)

To create the column:

1. Open Power BI Desktop and select the Data or Model view.
2. Select the table that contains the relevant fields.
3. Choose New column from the ribbon.
4. Enter the DAX formula in the formula bar.
5. Press Enter and check the results for several rows.
6. Use the new column in a table, chart, slicer, or filter when appropriate.

10.2 Example: Combined Eligibility Column

Eligibility Status =
IF(
    Sales[SalesAmount] >= 20000
        && Sales[Profit] >= 3000,
    "Eligible",
    "Not Eligible"
)

This is useful when eligibility must be stored for each transaction. Since calculated columns are evaluated during model processing or refresh, their stored values do not recalculate for each slicer selection in the same way measures do.

11. Using Conditional Logic in Measures

A measure calculates a result dynamically according to the current filter context. Measures are commonly used for totals, ratios, KPIs, and conditional outputs that should respond to slicers and visual selections.

11.1 Create a Basic Sales Measure

Total Sales =
SUM(Sales[SalesAmount])

11.2 Conditional Measure with IF

Sales Target Status =
IF(
    [Total Sales] >= 100000,
    "Target Achieved",
    "Target Not Achieved"
)

This measure evaluates the total sales in the current context. Its result can change by month, region, salesperson, or other report filters.

11.3 Measure with AND Logic

Total Profit =
SUM(Sales[Profit])

Performance Status =
IF(
    [Total Sales] >= 100000
        && [Total Profit] >= 15000,
    "Strong Performance",
    "Review Performance"
)

This measure checks whether both aggregated metrics meet their thresholds in the current context. Make sure the thresholds and aggregation levels match the business definition of performance.

11.4 Measure with SWITCH

Sales Performance Label =
SWITCH(
    TRUE(),
    [Total Sales] >= 200000, "Excellent",
    [Total Sales] >= 100000, "Good",
    [Total Sales] >= 50000, "Average",
    "Low"
)

Place this measure in a card, matrix, or table to display a context-sensitive performance label. Because measures can be evaluated at different aggregation levels, test the output in each visual where it will be used.

12. Calculated Columns vs Measures for Conditional Logic

Best practice: Use a calculated column when the classification belongs to an individual row and must be available for grouping. Use a measure when the result depends on aggregated values or should respond dynamically to report filters.

13. Practical Business Examples

13.1 Employee Performance Classification

Suppose employee performance depends on achieving both a sales target and a customer satisfaction score.

Employee Performance =
IF(
    Employees[SalesTargetAchieved] = TRUE()
        && Employees[CustomerSatisfaction] >= 4,
    "High Performer",
    "Needs Improvement"
)

This is a row-level example suitable for a calculated column when both fields are available in the same employee row. If performance is based on aggregated sales and satisfaction measures, adapt the formula to use measures instead.

13.2 Discount Eligibility

Customers may receive a discount if they are members or if their purchase amount exceeds a required threshold.

Discount Eligibility =
IF(
    Customers[MembershipStatus] = "Member"
        || Customers[PurchaseAmount] >= 10000,
    "Discount Eligible",
    "No Discount"
)

This rule uses OR because either membership or purchase value is sufficient. In a real system, confirm that this rule matches the organization's actual discount policy.

13.3 Inventory Reorder Alert

A warehouse may need to reorder an item when its stock level is below the reorder point and the item is active.

Inventory Alert =
IF(
    Inventory[IsActive] = TRUE()
        && Inventory[StockQuantity] < Inventory[ReorderPoint],
    "Reorder Required",
    "Stock Sufficient"
)

The AND condition ensures that inactive products are not flagged, even when their stock quantity is low.

13.4 Customer Risk Category

Customer Risk =
SWITCH(
    TRUE(),
    Customers[OverdueDays] > 60
        && Customers[OutstandingBalance] > 10000, "High Risk",
    Customers[OverdueDays] > 30
        || Customers[OutstandingBalance] > 5000, "Medium Risk",
    "Low Risk"
)

This example uses a high-risk rule followed by a medium-risk rule. The first matching rule wins. Validate the thresholds and ensure the order reflects the company's risk policy.

13.5 Order Escalation

Order Escalation =
SWITCH(
    TRUE(),
    Sales[OrderStatus] = "Cancelled", "Immediate Review",
    Sales[DeliveryDays] > 7
        && Sales[Priority] = "High", "Urgent Escalation",
    Sales[DeliveryDays] > 7, "Delivery Follow-up",
    "No Escalation"
)

The order of conditions matters. Cancelled orders are handled first; delayed high-priority orders are handled next; other delayed orders are handled afterward.

14. Step-by-Step: Build a Conditional Sales Report

This exercise combines the four key functions and operators in a single Power BI workflow.

Step 1: Prepare the Data

Use a sales table with fields such as:

Step 2: Create a Sales Category

Sales Category =
IF(
    Sales[SalesAmount] >= 20000,
    "High",
    "Standard"
)

Step 3: Create a Performance Rating

Performance Rating =
SWITCH(
    TRUE(),
    Sales[SalesAmount] >= 30000
        && Sales[Profit] >= 5000, "Excellent",
    Sales[SalesAmount] >= 20000, "Good",
    Sales[SalesAmount] >= 10000, "Average",
    "Low"
)

Step 4: Flag Orders That Need Attention

Order Attention =
IF(
    Sales[OrderStatus] = "Cancelled"
        || Sales[OrderStatus] = "Delayed",
    "Attention Required",
    "Normal"
)

Step 5: Create Total Measures

Total Sales =
SUM(Sales[SalesAmount])

Total Profit =
SUM(Sales[Profit])

Step 6: Create a Dynamic Target Status

Overall Target Status =
IF(
    [Total Sales] >= 100000
        && [Total Profit] >= 15000,
    "Both Targets Achieved",
    "One or More Targets Missed"
)

Step 7: Build the Report

1. Add a card visual for Total Sales.
2. Add another card for Total Profit.
3. Create a table showing OrderID, SalesAmount, Profit, Sales Category, Performance Rating, and Order Attention.
4. Add a bar chart comparing sales by Region.
5. Add slicers for Region and OrderStatus.
6. Test how the measure-based target status changes when slicers are used.
7. Verify that row-level classifications remain consistent with their source-row values.

15. Common Errors and Troubleshooting

15.1 Incorrect Condition Order

Problem: A formula assigns a record to a lower category even though it meets a higher threshold.

Cause: A broad or low threshold is evaluated before a more restrictive threshold.

Solution: In nested IF or SWITCH(TRUE()), arrange range conditions from the highest or most specific threshold to the lowest.

Sales Rating =
SWITCH(
    TRUE(),
    Sales[SalesAmount] >= 30000, "Excellent",
    Sales[SalesAmount] >= 20000, "Good",
    Sales[SalesAmount] >= 10000, "Average",
    "Low"
)

15.2 Mixing Text and Numeric Values

Problem: A conditional expression produces inconsistent data types or an unexpected result.

Cause: Different branches return incompatible values, such as a number in one branch and text in another.

Solution: Keep return types consistent wherever possible. For a bonus measure, return numbers from all branches; for a category label, return text from all branches.

Bonus Amount =
IF(
    [Total Sales] >= 100000,
    1000,
    0
)

15.3 Missing Default Result

Problem: SWITCH returns a blank for an unexpected category.

Cause: No listed value matches and no default result is provided.

Solution: Add a final default result, such as "Other" or "Unknown".

Region Label =
SWITCH(
    Sales[Region],
    "North", "Northern Region",
    "South", "Southern Region",
    "Other"
)

15.4 Confusing AND with OR

Problem: Records qualify even though they do not meet all requirements, or fail when one qualifying condition is sufficient.

Cause: AND and OR represent different business rules.

Solution: Translate the requirement into plain language before coding. "Both" usually indicates AND; "either" or "at least one" usually indicates OR.

15.5 Incorrect Column or Table Names

Problem: Power BI displays an error because a referenced field cannot be found.

Cause: The formula uses an incorrect table or column name.

Solution: Select the field from the Fields or Data pane when possible, and verify spelling and table names. Column references commonly use the format TableName[ColumnName].

15.6 Incorrect Use of a Measure in Row-Level Logic

Problem: A formula does not behave as expected when used as a calculated column.

Cause: A measure is evaluated in filter context, while calculated columns are evaluated row by row during model processing.

Solution: Decide whether the business rule applies to each row or to an aggregated result. Use direct column references for row-level classifications and measures for dynamic aggregated logic where appropriate.

15.7 Blank Values and Missing Data

Problem: Records with missing data are assigned an unexpected category.

Cause: The formula does not explicitly account for blanks or missing source values.

Solution: Decide how missing values should be handled and test them. For example:

Sales Data Status =
IF(
    ISBLANK(Sales[SalesAmount]),
    "Missing Sales",
    IF(
        Sales[SalesAmount] >= 10000,
        "High Sales",
        "Low Sales"
    )
)

This formula distinguishes missing sales from a genuine low-sales value. Do not assume that a blank and zero mean the same thing.

16. Best Practices for Writing Conditional DAX

• Keep formulas readable: Use indentation and line breaks for nested logic.
• Use meaningful names: Name columns and measures according to the business meaning of their results.
• Order conditions carefully: Evaluate high-priority and more restrictive rules first when using SWITCH(TRUE()).
• Provide default results: Avoid unexplained blanks for unexpected categories unless blanks are intentional.
• Use parentheses: Make combinations of AND and OR easy to interpret.
• Choose the correct calculation type: Use columns for row-level labels and measures for context-sensitive aggregations.
• Validate thresholds: Confirm the numerical boundaries with the business owner.
• Test boundary values: Test values exactly equal to thresholds, just below them, and just above them.
• Handle missing data: Decide whether blanks should return a label, a blank, or a fallback value.
• Avoid unnecessary complexity: Prefer a clear SWITCH expression or reusable measures over long, difficult-to-maintain formulas.
• Test with slicers: Verify that measures respond correctly to report filters and visual context.
• Document important rules: Add descriptions to measures or maintain a data dictionary for business definitions.

17. Practice Exercises

Complete the following exercises using a sample Power BI sales dataset. Try writing each formula yourself before checking the suggested solution.

Exercise 1: Create a Sales Category with IF

Requirement: If the sales amount is 15,000 or more, display "Qualified". Otherwise, display "Not Qualified".

Sales Qualification =
IF(
    Sales[SalesAmount] >= 15000,
    "Qualified",
    "Not Qualified"
)

Expected learning: Understanding the logical test and the true and false results of IF.

Exercise 2: Classify Sales with SWITCH(TRUE())

Requirement: Classify sales as follows:

• 30,000 or more: "Platinum"
• 20,000 or more: "Gold"
• 10,000 or more: "Silver"
• Below 10,000: "Bronze"

Sales Tier =
SWITCH(
    TRUE(),
    Sales[SalesAmount] >= 30000, "Platinum",
    Sales[SalesAmount] >= 20000, "Gold",
    Sales[SalesAmount] >= 10000, "Silver",
    "Bronze"
)

Expected learning: Ordering range conditions correctly and defining a default result.

Exercise 3: Use AND for Bonus Eligibility

Requirement: A salesperson receives a bonus when sales are at least 25,000 and profit is at least 4,000.

Bonus Eligibility =
IF(
    Sales[SalesAmount] >= 25000
        && Sales[Profit] >= 4000,
    "Bonus Approved",
    "Bonus Not Approved"
)

Expected learning: Requiring all conditions to be true.

Exercise 4: Use OR for Order Alerts

Requirement: Flag an order if it is delayed or cancelled.

Order Alert =
IF(
    Sales[OrderStatus] = "Delayed"
        || Sales[OrderStatus] = "Cancelled",
    "Alert",
    "Normal"
)

Expected learning: Creating a result when at least one condition is true.

Exercise 5: Combine AND and OR

Requirement: Flag a transaction if it is either a high-value transaction with low profit, or a cancelled order.

Transaction Flag =
IF(
    (
        Sales[SalesAmount] > 50000
            && Sales[Profit] < 2000
    )
        || Sales[OrderStatus] = "Cancelled",
    "Flagged",
    "Not Flagged"
)

Expected learning: Grouping conditions with parentheses and combining logical operators.

Exercise 6: Build a Dynamic Measure

Requirement: Display "Target Achieved" when total sales reach 100,000; otherwise, display "Target Pending".

Total Sales =
SUM(Sales[SalesAmount])

Sales Target Result =
IF(
    [Total Sales] >= 100000,
    "Target Achieved",
    "Target Pending"
)

Expected learning: Using conditional logic with a measure that responds to report filters.

18. Mini Project: Sales Performance and Order Monitoring Dashboard

18.1 Project Objective

Create a Power BI dashboard that classifies sales performance, evaluates bonus eligibility, and identifies orders requiring attention. The project should demonstrate IF, SWITCH, AND, and OR in a practical business reporting scenario.

18.2 Required Dataset Columns

18.3 Create the Required Calculations

1. Sales category:

Sales Category =
IF(
    Sales[SalesAmount] >= 20000,
    "High Value",
    "Standard Value"
)

2. Sales performance:

Sales Performance =
SWITCH(
    TRUE(),
    Sales[SalesAmount] >= 30000, "Excellent",
    Sales[SalesAmount] >= 20000, "Good",
    Sales[SalesAmount] >= 10000, "Average",
    "Low"
)

3. Bonus eligibility:

Bonus Eligibility =
IF(
    Sales[SalesAmount] >= 25000
        && Sales[Profit] >= 4000,
    "Eligible",
    "Not Eligible"
)

4. Order monitoring:

Order Monitoring =
IF(
    Sales[OrderStatus] = "Delayed"
        || Sales[OrderStatus] = "Cancelled"
        || Sales[Priority] = "High",
    "Review",
    "Normal"
)

5. Total sales and profit measures:

Total Sales =
SUM(Sales[SalesAmount])

Total Profit =
SUM(Sales[Profit])

6. Overall performance measure:

Overall Performance =
SWITCH(
    TRUE(),
    [Total Sales] >= 200000
        && [Total Profit] >= 30000, "Excellent",
    [Total Sales] >= 100000
        || [Total Profit] >= 15000, "Satisfactory",
    "Needs Attention"
)

18.4 Build the Dashboard

1. Add card visuals for Total Sales and Total Profit.
2. Create a column chart showing sales by region.
3. Add a table showing order details and the calculated classifications.
4. Use a slicer for salesperson and another for region.
5. Add a visual that shows orders classified as "Review".
6. Use the Overall Performance measure in a card or KPI-style visual.
7. Test the dashboard with different slicer selections.

18.5 Project Validation Checklist

• Confirm that sales categories use the correct thresholds.
• Verify that the highest performance category is evaluated first.
• Test bonus eligibility when only one of the two requirements is met.
• Check that delayed, cancelled, and high-priority orders are flagged correctly.
• Confirm that Total Sales and Total Profit change with report filters.
• Check how blank or unexpected source values are classified.
• Ensure the dashboard labels are understandable to business users.

19. Interview Questions and Answers

Q1. What is the IF function in Power BI?

Answer: IF is a DAX function that evaluates a logical condition and returns one value when the condition is true and another when it is false. It is commonly used for classifications, flags, and conditional calculations.

Q2. What is the syntax of IF?

Answer: IF(logical_test, value_if_true, value_if_false). The false-result argument is optional. If it is omitted and the condition is false, IF returns BLANK.

Q3. What is a nested IF statement?

Answer: A nested IF is an IF function placed inside another IF function. It allows several conditions to be evaluated in sequence, although long nested formulas can become difficult to maintain.

Q4. What is the SWITCH function?

Answer: SWITCH compares an expression with a list of values and returns the result associated with the first match. It can replace long nested IF expressions when comparing one expression against multiple values.

Q5. What is SWITCH(TRUE()) used for?

Answer: SWITCH(TRUE()) is a common DAX pattern for evaluating multiple logical conditions in order. It returns the result associated with the first condition that evaluates to TRUE.

Q6. What is the difference between IF and SWITCH?

Answer: IF is ideal for a true/false decision. SWITCH is useful when matching one expression against several possible values. SWITCH(TRUE()) is useful for several ordered conditions, such as numerical ranges.

Q7. What does AND do in DAX?

Answer: AND returns TRUE only when both supplied logical conditions are true. For multiple conditions, the && operator is often more convenient.

Q8. What does OR do in DAX?

Answer: OR returns TRUE when at least one supplied logical condition is true. The || operator can be used to combine multiple alternative conditions.

Q9. What is the difference between AND and OR?

Answer: AND requires every condition to be true. OR requires only one or more conditions to be true.

Q10. Can AND and OR be combined in the same formula?

Answer: Yes. Use && and || together, and add parentheses to make the intended grouping clear.

Q11. Why is condition order important in SWITCH(TRUE())?

Answer: SWITCH returns the result for the first matching condition. If a broad condition is placed before a more restrictive condition, the restrictive condition may never be reached.

Q12. What happens when SWITCH has no matching value and no default result?

Answer: SWITCH returns BLANK when none of the specified values matches and no default result is supplied.

Q13. Can conditional DAX return numbers?

Answer: Yes. IF and SWITCH can return numbers, text, or other scalar results. Keep result types consistent where possible, particularly when building a SWITCH expression.

Q14. Can IF and SWITCH be used in measures?

Answer: Yes. They can be used in calculated columns and measures. A measure evaluates according to the current filter context, whereas a calculated column is evaluated row by row during model processing.

Q15. When should you use a calculated column instead of a measure?

Answer: Use a calculated column for row-level classifications that need to be stored, grouped, or used in slicers. Use a measure for dynamic calculations that should respond to filters and aggregations.

Q16. How can missing values be handled in conditional expressions?

Answer: Use functions such as ISBLANK to explicitly test for missing values and decide whether to return a label, a blank, or another suitable result.

Q17. Why might a conditional measure change when a slicer is selected?

Answer: Measures are evaluated in the current filter context. When a slicer changes the filtered data, the aggregated values used by the condition may change, causing a different result.

Q18. How can you debug a complicated conditional formula?

Answer: Break the logic into smaller steps, check each condition separately, verify column names and data types, test boundary values, use parentheses, and compare the result against known sample records.

20. Quick Revision Notes

20.1 Essential Rules to Remember

• IF evaluates a condition and returns a true or false result.
• SWITCH is useful for matching a value against multiple options.
• SWITCH(TRUE()) checks multiple conditions in order.
• AND requires every condition to be true.
• OR requires at least one condition to be true.
• Use parentheses to group complex conditions clearly.
• Place the most restrictive conditions first in SWITCH(TRUE()).
• Include a default result when unexpected values need to be handled.
• Use calculated columns for stored row-level classifications and measures for dynamic calculations.
• Test values at, above, and below each threshold.

21. Additional Learning Resources

• Microsoft Learn: IF Function (DAX)
• Microsoft Learn: SWITCH Function (DAX)
• Microsoft Learn: AND Function (DAX)
• Microsoft Learn: OR Function (DAX)
• Microsoft Learn: DAX Operators
• Microsoft Power BI Training – JustAcademy
• Register for a Course Demo
• Visit JustAcademy

22. Conclusion

IF, SWITCH, AND, and OR are essential tools for building conditional logic in Power BI. IF is suited to true/false decisions, SWITCH simplifies multiple-value matching, AND checks whether all required conditions are true, and OR checks whether at least one condition is true.

Combining these functions and operators enables analysts to create performance classifications, eligibility rules, customer segments, inventory alerts, and order-monitoring reports. Reliable results depend on understanding the business requirement, selecting the correct calculation type, ordering conditions properly, handling missing data, and testing boundary cases.

Practice these expressions with a sample dataset, then apply them to calculated columns and measures in a Power BI report. As your formulas become more complex, prioritize readable DAX, clear business rules, and careful validation.