Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
jpc
Helper I
Helper I

Conditional format a matrix to highlight differences within same row

Objective - highlight the differences between products on a row by row basis

 

So, in this simple example, I select a few 'recipes' from a slicer, which adds a few columns to the matrix.   On each row (ie for each type) I want to colour them based on their description, so use a different colour when there is a difference.   This is to help the user compare a small number of recipes and easily spot and check the differences. 

 

jpc_1-1702035661821.png

 

I am currently doing this example with conditional formatting.   My very basic approach here is using a calculated column based on the length of the description - it sort of creates the effect i am aiming for, but obviously can't show a difference between two items with the same description length (apple and peach are both the same colour).   

 

I wondered about building some more complex calculation based on each unichar code in the string, or a checksum or hash of the string, but looking for examples that doesn't seem as straightforward as i'd hoped, and may be overkill.

 

Ideally i'd like to really draw attention to the different cells, as some text fields in my dataset are pretty similar and could be easily missed - so, while this example is a gradient filter I think a better approach could be one where an integer is assigned and incremented each time a difference is encountered - sort of like a running total, but modified by the slicer/filter so we are always dealing with 1s,2s,3s etc.  This would be simple to set up a conditional format so adjacent values are really obvious. 

 

(I got stuck with this, as running total examples I found are generally calculated columns based on date field) - but this would mean the above table is coloured with a field returning mostly 1s, just with pommegrante = 2 and peach = 3 and then onion = 2 (as each row encountered differences)

 

 

I'm sure there are several ways to approach this seemingly simple problem - i'd really apprecaite some guidance what direction to take!

 

 

Sample data:

 

Recipe_IDTypeDescription
1FruitApple
1VegetableCarrot
2FruitPomegranate
2VegetableCarrot
3FruitApple
3VegetableOnion
4FruitApple
4VegetableOnion
5FruitPeach
5VegetableCarrot

 

 

 

 

 

2 REPLIES 2
Anonymous
Not applicable

Hi @jpc ,

Below is my table:

vxiandatmsft_0-1702432916902.png

The following Dax might work for you:

Column = 
   var _app5 = LEN('Table'[Description])
   RETURN
     SWITCH(
        TRUE(),
        _app5 <= 5 , 1 ,
        _app5 <10 && _app5 > 5 , 2,
        _app5 < 15 && _app5 >10 , 3
     )

The final output is shown in the following figure:

vxiandatmsft_1-1702432976833.png

vxiandatmsft_2-1702433009119.png

Best Regards,

Xianda Tang

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

 

Thank you for taking the time to read my question, and build a solution.

 

Unfortunately, i still see in your example that Apple and Peach are still the same colour.   Length of the string is not an effective method, i am looking to improve on this with some string comparison, if you have any ideas on this?

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.