How to replace values if condition is true in Power BI?

Let’s assume that your product team wants to categorize items based on their profit margin. Part of data pre-processing is to replace numerical values with descriptive labels. How can you implement this conditional value replacement to quickly identify high-performing and underperforming products?

In this tutorial we will use both Power Query and DAX to replace values based on conditions. Our ‘Products’ table has columns: ProductID (Number), ProductName (Text), Category (Text), and ProfitMargin (Percentage).

Change column values conditionally using Power Query

  1. In Power BI Desktop, click ‘Transform data’ on the Home tab.
  2. In Power Query Editor, go to ‘Add Column’ tab and click ‘Conditional Column’.
  3. Set up the following rules:
  • If ‘ProfitMargin’ is greater than or equal to 0.30, then the cell value will read “High Margin”
  • Else if ‘ProfitMargin’ is greater than or equal to 0.15, then “Medium Margin”
  • Else “Low Margin”
  1. Name the new column “Margin Category” and click ‘OK’.

Alternatively, you can use Power Query M code to accomplish a similar result:

= Table.AddColumn(Products, "Margin Category", each 
    if [ProfitMargin] >= 0.30 then "High Margin"
    else if [ProfitMargin] >= 0.15 then "Medium Margin"
    else "Low Margin")

Replace Power Bi data conditionally using DAX

An alternative option is to use DAX expressions. Difference being that the calculations are made later, as part of the data analysis process. This typically taxes the overall performance of our reporting system.

  1. In Power BI Desktop, go to the ‘Data’ view.
  2. Select the ‘Products’ table.
  3. Click ‘New Column’ and enter the following DAX formula:
Margin Category = 
SWITCH(
    TRUE(),
    Products[ProfitMargin] >= 0.30, "High Margin",
    Products[ProfitMargin] >= 0.15, "Medium Margin",
    "Low Margin"
)

To use in the data in a Power BI visual:

  1. Create a matrix visualization.
  2. Add ‘Category’ to Rows and ‘Margin Category’ to Columns.
  3. Add a count of ProductID to Values.

This will show the distribution of margin categories across product categories.

Troubleshooting: Ensure ProfitMargin is formatted as a percentage. For complex conditions, consider using nested IF() statements in DAX.