Forum Discussion

Heinrich's avatar
Heinrich
Post Partisan
2 years ago
Solved

Compare 2 rows and highlight differences

Hello I have a table visual with 4 columns I would like to compare column A with column C and see if on the same row A+C match. If not highlight it or filter it out Regards Heinrich
Need Help
Show and Tell
Tips and Tricks
  • 123abc's avatar
    123abc
    2 years ago

    To compare two columns (Column A and Column C) within the same row and highlight the differences or filter out the rows where they don't match in Power BI, you can follow these steps:

    1. Create a New Calculated Column: Start by creating a calculated column that checks whether Column A matches Column C for each row. If they match, it will return a specific value (e.g., "Match"), and if they don't match, it will return a different value (e.g., "No Match").

      In Power BI Desktop, go to the "Model" view, select your table, and create a new calculated column with the following DAX formula:

    MatchStatus = IF([Column A] = [Column C], "Match", "No Match")

     

    1. This formula compares the values in Column A and Column C and assigns "Match" or "No Match" accordingly.

    2. Apply Conditional Formatting: Next, you can apply conditional formatting to visually highlight the differences in your table visual. Here's how you can do it:

      • Select the table visual that you want to format.
      • Go to the "Format" pane on the right.
      • Under "Conditional formatting," select "Background color."
      • Choose the "MatchStatus" column you created as the field to base the formatting on.
      • Define your formatting options, such as setting a background color for "No Match."

      This will highlight the rows where Column A and Column C do not match based on your conditional formatting settings.

    3. (Optional) Filter Rows: If you also want to filter out the rows where Column A and Column C do not match, you can create a visual-level filter. Here's how:

      • Add a slicer or filter visual to your report.
      • Use the "MatchStatus" column as the filter field.
      • Select the value "No Match" to filter out the rows where Column A and Column C do not match.

    Now, when you interact with the filter, it will hide the rows where there is no match between Column A and Column C, and your table visual will show only the matching rows with conditional formatting to highlight the differences.

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

123abc's avatar
123abc
Community Champion
2 years ago

To compare two columns (Column A and Column C) within the same row and highlight the differences or filter out the rows where they don't match in Power BI, you can follow these steps:

  1. Create a New Calculated Column: Start by creating a calculated column that checks whether Column A matches Column C for each row. If they match, it will return a specific value (e.g., "Match"), and if they don't match, it will return a different value (e.g., "No Match").

    In Power BI Desktop, go to the "Model" view, select your table, and create a new calculated column with the following DAX formula:

MatchStatus = IF([Column A] = [Column C], "Match", "No Match")

 

  1. This formula compares the values in Column A and Column C and assigns "Match" or "No Match" accordingly.

  2. Apply Conditional Formatting: Next, you can apply conditional formatting to visually highlight the differences in your table visual. Here's how you can do it:

    • Select the table visual that you want to format.
    • Go to the "Format" pane on the right.
    • Under "Conditional formatting," select "Background color."
    • Choose the "MatchStatus" column you created as the field to base the formatting on.
    • Define your formatting options, such as setting a background color for "No Match."

    This will highlight the rows where Column A and Column C do not match based on your conditional formatting settings.

  3. (Optional) Filter Rows: If you also want to filter out the rows where Column A and Column C do not match, you can create a visual-level filter. Here's how:

    • Add a slicer or filter visual to your report.
    • Use the "MatchStatus" column as the filter field.
    • Select the value "No Match" to filter out the rows where Column A and Column C do not match.

Now, when you interact with the filter, it will hide the rows where there is no match between Column A and Column C, and your table visual will show only the matching rows with conditional formatting to highlight the differences.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.