Conditional Sales Categorization in DAX

DAX Formula for Conditional Logic

Description

Create a DAX formula that uses multiple conditions (similar to IF statements in Excel) to evaluate different scenarios. For instance, if the Sales value is greater than 1000, return "High"; if the Sales value is between 500 and 1000, return "Medium"; and if the Sales value is less than 500, return "Low".

DAX Formula

Category =
SWITCH(
    TRUE(),
    [Sales] > 1000, "High",
    [Sales] >= 500, "Medium",
    [Sales] < 500, "Low",
    "Unknown"
)

Explanation

1. SWITCH(TRUE(), ...): The SWITCH function evaluates a list of expressions and returns the first result where the expression is true. Using TRUE() as the first argument makes this function act like multiple IF statements.
2. Condition [Sales] > 1000: Evaluates whether the sales value is greater than 1000. If true, returns "High".
3. Condition [Sales] >= 500: Evaluates whether the sales value is between 500 and 1000 (since it's checked after the first condition). If true, returns "Medium".
4. Condition [Sales] < 500: Evaluates whether the sales value is less than 500. If true, returns "Low".
5. Default "Unknown": Provides a default value in case none of the conditions are met.

Practical Example

Consider a table SalesData with the following data:

Using the formula above, the resulting table with the new Category column would be:

Application and Benefits

• Categorization: Useful for categorizing numerical data into different segments based on defined thresholds.
• Simplification: Reduces the need for nested IF statements and makes the DAX code easier to read and maintain.

For further learning and advanced topics in DAX, consider exploring courses and resources on the Enterprise DNA Platform.