Conditional Measures with IF or SWITCH

Step 1: Simple Logic with the IF Function

The most fundamental conditional function in DAX is IF. It evaluates a condition and returns one value if the condition is TRUE, and another if it's FALSE.

The syntax is: IF(<logical_test>, <value_if_true>, <value_if_false>)

Let's say we want to create a simple, two-category status based on our [Sales Variance (%)] measure:

• If the variance is 0% or higher, performance is "Good".
• If the variance is negative, performance is "Poor".

The DAX measure would be:

Performance Status (Simple) = 
IF(
    [Sales Variance (%)] >= 0, 
    "Good", 
    "Poor"
)

This works perfectly for binary outcomes. But what if we need more nuance? What if we want three or more categories, such as 'Below Target', 'On Target', and 'Exceeded Target'? We could "nest" IF functions inside each other, but this quickly becomes messy and difficult to read.

As you can see, the nested IF is hard to follow. There's a better way.

Step 2: Handling Multiple Conditions with SWITCH

The SWITCH function is DAX's elegant solution for handling multiple conditions. It evaluates an expression and compares the result against a list of value-result pairs.

For our purpose, the most powerful pattern is SWITCH(TRUE(), ...). This structure allows you to test a series of logical expressions in order and returns the result for the first one that evaluates to TRUE. It is the professional standard for creating range-based categories.

This video from Pragmatic Works provides an excellent walkthrough, starting from the basic IF function and building up to the SWITCH(TRUE()) pattern.

This table provides a quick summary of when to use IF versus SWITCH.

Step 3: Your Turn - Create the Performance Status Measure

Now, let's apply this knowledge to our project. We'll create a measure that categorizes performance into three distinct bands based on the [Sales Variance (%)] measure from our last lesson.

Business Logic:

• If variance is greater than 5% (0.05), the status is "Exceeded Target".
• If variance is between 0% and 5%, the status is "On Target".
• If variance is less than 0%, the status is "Below Target".

In your Power BI file, create the following new measure. We'll use a VAR (variable) to store the result of [Sales Variance (%)], which makes the formula cleaner and more efficient.

Performance Status = 
VAR SalesVariance = [Sales Variance (%)]
RETURN
    SWITCH(TRUE(),
        SalesVariance > 0.05, "Exceeded Target",
        SalesVariance >= 0, "On Target",
        "Below Target"
    )

Note the order of evaluation: SWITCH stops at the first TRUE condition. We check for > 0.05 first. If that's false, we then check for >= 0. If that's also false, it must be negative, so we return the default value "Below Target".

Once you've created the measure, add it as a new column to the Matrix visual you created in the last lesson. You should now see a descriptive status for each month, making the report much easier to interpret.

Step 4: A Professional Application - Creating KPI Statuses

The SWITCH(TRUE()) pattern is a fundamental building block for creating formal Key Performance Indicators (KPIs) in Power BI. Often, instead of returning text like "On Target", analysts return a number (e.g., -1 for bad, 0 for neutral, 1 for good). These numbers can then be used to drive visual indicators like traffic lights or icons.

The following article from SQLBI, a leading authority on DAX, demonstrates this exact technique.

This approach of returning a numerical status is extremely powerful and flexible, and it directly leads into our next topic.

Conclusion

Great work today! You have learned how to add a crucial layer of business intelligence to your report by translating raw numbers into clear, actionable statuses. This skill separates a basic data table from a true performance dashboard.

Key Takeaways:

• IF vs. SWITCH: Use IF for simple binary logic and SWITCH for cleaner, more readable handling of multiple conditions.
• The SWITCH(TRUE()) Pattern: This is the go-to technique in DAX for categorizing values based on ranges or a series of logical tests.
• Text vs. Numbers: You can return descriptive text for clarity in tables or return numbers (-1, 0, 1) to enable more advanced visual techniques.

Preview of the Next Lesson:

In our next and final lesson, we'll bring our report to life. You will learn how to use the numerical status logic from the SQLBI article to create dynamic conditional formatting, automatically coloring your visuals based on performance. We will also cover other powerful final touches, such as creating dynamic report titles and reviewing your measures for performance.