Conditional Logic Function in Power BI

In this guide, we will create a DAX function in Power BI that replicates the Excel-like conditional logic provided. The function categorizes different scenarios based on the values in two columns (L and M).

Function Definition

The primary objective is to evaluate the input conditions concerning specific text matches and return corresponding category names.

DAX Function

CategorizeIssues = 
SWITCH(
    TRUE(),
    AND(L2 = "DS-GM", ISNUMBER(SEARCH("1", M2))), "Center Pit",
    AND(L2 = "DS-GM", ISNUMBER(SEARCH("2", M2))), "Generator marks",
    AND(L2 = "DS-GM", ISNUMBER(SEARCH("3", M2))), "Miscellaneous",
    AND(L2 = "DS-DL", ISNUMBER(SEARCH("1", M2))), "No polish",
    AND(L2 = "DS-DL", ISNUMBER(SEARCH("2", M2))), "Polish scratches",
    AND(L2 = "DS-DL", ISNUMBER(SEARCH("3", M2))), "Incomplete polish",
    AND(L2 = "DS-DL", ISNUMBER(SEARCH("4", M2))), "Miscellaneous",
    AND(L2 = "FN-MO", ISNUMBER(SEARCH("2A", M2))), "FTD-Out of range",
    AND(L2 = "FN-MO", ISNUMBER(SEARCH("2B", M2))), "FTD-Clear lens frame pushed with Polar",
    AND(L2 = "FN-MO", ISNUMBER(SEARCH("2C", M2))), "FTD-No new frame pushed in auto",
    AND(L2 = "FN-MO", ISNUMBER(SEARCH("2D", M2))), "FTD-Wrong frame validated FTC",
    AND(L2 = "FN-MO", ISNUMBER(SEARCH("2W", M2))), "Non-FTD-TBT FTD not meeting criteria",
    AND(L2 = "FN-MO", ISNUMBER(SEARCH("2X", M2))), "Non-FTD-Nylor job with polar lenses",
    AND(L2 = "FN-MO", ISNUMBER(SEARCH("2Y", M2))), "Non-FTD-FTD or 499 nylor job with polar lenses",
    AND(L2 = "FN-FM", ISNUMBER(SEARCH("1", M2))), "FTD trace quality",
    AND(L2 = "FN-FM", ISNUMBER(SEARCH("2", M2))), "Sizing adjustment",
    AND(L2 = "FN-FM", ISNUMBER(SEARCH("3", M2))), "Key in UPC error",
    AND(L2 = "WH-PD", ISNUMBER(SEARCH("3", M2))), "Cosmetic defect",
    AND(L2 = "WH-PD", ISNUMBER(SEARCH("4", M2))), "Mixed lenses in the box",
    AND(L2 = "WH-PD", ISNUMBER(SEARCH("5", M2))), "Incorrect base curve",
    AND(L2 = "WH-PD", ISNUMBER(SEARCH("6", M2))), "Delamination",
    "Not found" // Default return value if no conditions are met
)

Explanation

• Function Name: CategorizeIssues
• Logic Structure: The use of the SWITCH(TRUE(), ...) construct allows for a cleaner multi-condition evaluation compared to nested IF statements.
• Functions Utilized:
  • AND to combine conditions.
  • ISNUMBER and SEARCH to check for the presence of specific substrings in column M2.
• Return Values: Each logical branch returns a specific category based on the defined conditions. If no match is found, it defaults to "Not found".

How to Use

1. Create a New Measure: Open Power BI Desktop, navigate to the "Data" view, select your table, and add a new measure. Paste the above DAX function.

2. Visualize Results: You can drag this measure into a table or other visuals to categorize issues based on columns L and M.

Example Usage

Suppose you have a dataset with the following values:

Using the CategorizeIssues measure, you would get:

This showcases how the logic neatly categorizes various scenarios based on the given rules.

Conclusion

DAX functions like the one created provide powerful tools for data transformation and categorization in Power BI. Adopting best practices for readability and efficiency is key for maintaining high-quality code as your dataset grows. For in-depth learning on DAX and Power BI, consider exploring resources available through the Enterprise DNA Platform.