cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelH Regular Visitor
Regular Visitor

Conditional formatting based on multiple conditions

Hi,

 

In matrices, is it possible to have conditional formatting based on more than one rule?

Ie based on column value (cabin) and aggregation value sum(seats remaining)

(Note I am not talking about formulaic statements - this is to do with formatting cells)

 

Eg

If Cabin = 'Business' and Seats between 10 and 40 then format Green

If Cabin = 'Economy' and Seats between 10 and 40 then format Orange

etc etc

 

CabinSeatsColour
Business30Green
Business10Orange
Economy30Orange
Economy10Red
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Conditional formatting based on multiple conditions

Hi @MichaelH

 

Can you try this?

 

Create a measure

 

Condition = MAXX(Ex_Format, 
   IF(Ex_Format[Cabin]="Business"&& Ex_Format[Seats] >10, 1,
        IF(Ex_Format[Cabin]="Business"&& Ex_Format[Seats] <=10,2,
            IF(Ex_Format[Cabin]="Economy"&& Ex_Format[Seats] <=10, 3,
                IF(Ex_Format[Cabin]="Economy"&& Ex_Format[Seats] >10 && Ex_Format[Seats]<=40, 2)))))

 

Here, 1 -> Green. 2-> Orange, 3-> Red.

 

and then do the conditional formatting like this:

 COndi.Format1.PNG

your result will be like this:

 

Condi.Format2.PNG

 

 

Hope this is what you are looking for.

 

Thanks

Raj

10 REPLIES 10
Super User
Super User

Re: Conditional formatting based on multiple conditions

Hey,

 

unfortunately this is not possible.

Currently it's not possible to correlate the color of a conditional formatting to the return value of a measure.

 

But hopefully this will change someday, but in the meantime you maybe find this https://dataveld.com/2018/07/16/use-svg-images-in-power-bi-part-3/ or some earlier blogs posts helpful. 

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Conditional formatting based on multiple conditions

Hi @MichaelH

 

Can you try this?

 

Create a measure

 

Condition = MAXX(Ex_Format, 
   IF(Ex_Format[Cabin]="Business"&& Ex_Format[Seats] >10, 1,
        IF(Ex_Format[Cabin]="Business"&& Ex_Format[Seats] <=10,2,
            IF(Ex_Format[Cabin]="Economy"&& Ex_Format[Seats] <=10, 3,
                IF(Ex_Format[Cabin]="Economy"&& Ex_Format[Seats] >10 && Ex_Format[Seats]<=40, 2)))))

 

Here, 1 -> Green. 2-> Orange, 3-> Red.

 

and then do the conditional formatting like this:

 COndi.Format1.PNG

your result will be like this:

 

Condi.Format2.PNG

 

 

Hope this is what you are looking for.

 

Thanks

Raj

MichaelH Regular Visitor
Regular Visitor

Re: Conditional formatting based on multiple conditions

Hey Raj,

 

Thank so much for your suggestion, it inspired my final solution which gives a load factor %.

I ended up going with the below:

 

Formatting Measure = maxx(TABLE_1,
if( TABLE_1[cabin] = "Economy", TABLE_1[SEG_SEATS_AVAILABLE] /  250  *max seats in Y,

if( TABLE_1[cabin] = "Business", TABLE_1[SEG_SEATS_AVAILABLE] /  50  *max seats in Y,

1)

 

 

Thanks so much!

Super User
Super User

Re: Conditional formatting based on multiple conditions

Hi @MichaelH

 

Glad that it solved your problem . Please mark the solution as accepted solution, so that the thread can be closed and it will help others as well.

 

Thanks
Raj

nmck86 Member
Member

Re: Conditional formatting based on multiple conditions

nmck86 Member
Member

Re: Conditional formatting based on multiple conditions

Hey Raj,

 

maybe you can assist with this as it is very similar.

 

 

https://community.powerbi.com/t5/Desktop/Question-about-a-SUMIF-calculation-in-Power-BI/m-p/464867#M...

Highlighted
Super User
Super User

Re: Conditional formatting based on multiple conditions

Thanks @nmck86 ... Just responded to that thread.

 

Thanks

Raj

a68tbird Member
Member

Re: Conditional formatting based on multiple conditions

Hello Raj,

  I came across your post while looking for a solution to my problem.  I created a measure just as you have suggested: 

 

 

Condition = 
MAXX(OrderItem,
    IF(OrderItem[Remaining Inventory] < OrderItem[Redeemed]*.2,1,
        IF(OrderItem[Remaining Inventory]< OrderItem[Redeemed] * .5,2,
            IF(OrderItem[Remaining Inventory] > OrderItem[Redeemed] *.5,3)
            )
       )
    )

but I don't seem to have the same options for applying this condition that you show in your screengrab. As you can see in my screenshot, I don't have the option to apply color to a column based on my measure. 

 

image.png

 

 

Am I missing something?

Thanks very much.

Travis

  

 

 

 

 

 

 

 

a68tbird Member
Member

Re: Conditional formatting based on multiple conditions

I guess the Conditional Formatting window has changed somewhat. I was able to replicate what I needed to do based on this screenshot:

 

image.png