Conditional formatting based on other row ?

[PunchBird]

Hi all,

I am trying to implement conditional formatting based on another row within the filter context but I can't get my head around how to do it.

I have a table that looks like this (but then with many car makes - for this question I just filtered one car make being Chevrolet):

Now, when a car ID has an 'original color' that is NULL and there is another row with the same car ID in the table where the value in original color is not NULL (in this example: Car ID 56356), the row where the original color is NULL should be highlighted in green, as displayed below (and otherwise highlighted red):

So basically, I need conditional formatting based on another row in the same table. How do I do this? @Greg_Deckler I read your post about row based conditional formatting but this does not seem to address what I am looking for. Can you or anyone help? @parry2k would you know? Many thanks!

[PunchBird]

@parry2k I tested it and the results are what should be expected. Thanks a lot for your help! 😃

[parry2k]

@PunchBird looks ok to me, test on your data and if you are getting the result then you are good to go.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PunchBird · Accepted solution]

@parry2k many thanks for your response. Your measure still wasn't completely correct because it made the first row in my example above green and the rest red. But thanks to your measure I think I was able to figure out what to do, below is my result. Does that seem right to you?

Color Measure = 
VAR __originalColor = CALCULATE ( MAX ('Table'[Original color] ), ALLEXCEPT ('Table','Table'[Id] ),'Table'[Original color] <> BLANK () )
VAR _originalId = CALCULATE ( MAX ('Table'[Id] ), ALLEXCEPT ('Table','Table'[Id] ),'Table'[Original color] <> BLANK () )
RETURN 
IF ( __originalColor <> BLANK () && _originalId = MAX('Table'[Id]) && MAX ('Table'[Original color] ) == BLANK (), "Green", "Red" )

[parry2k]

@PunchBird try this measure and then use this color for background color under conditional formatting, tweak the measure as you see fit

Color Measure = 
VAR __originalColor = CALCULATE ( MAX ('Table'[Color] ), ALLEXCEPT ('Table','Table'[Id] ),'Table'[Color] <> BLANK () )
RETURN 
IF ( __originalColor <> BLANK () && MAX ('Table'[Color] ) == BLANK (), "Green", "Red" )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak]

@PunchBird , Create a measure like this

color =
if(isblank(max(Table[original color])) && not(isblank(countx(filter(allselected(Table), Table[car ID] = max(Table[car ID])) && not(isblank(Table[original color]))) )), "Green", "red")

You this conditional formatting using field value option. For each column/field you have repeat this

[PunchBird]

@amitchandak still the same issue, there seem to be too many brackets? I double-checked but can't find what the issue is

[PunchBird]

Hi @amitchandak , thanks for your suggestion. I tried to add your measure but I get the error "Operator or expression '( )' is not supported in this context." Any ideas?

[amitchandak]

@PunchBird , Corrected the order , try

color =
if(isblank(max(Table[original color])) && not(isblank(countx(filter(allselected(Table), Table[car ID] = max(Table[car ID]) && not(isblank(Table[original color]))) ))) , "Green", "red")