Reply
Regular Visitor
Posts: 47
Registered: ‎02-01-2018
Accepted Solution

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

Accepted Solutions
Super User
Posts: 685
Registered: ‎11-01-2017

Re: Conditional formatting based on multiple conditions

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 1,439
Registered: ‎06-24-2015

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
Posts: 685
Registered: ‎11-01-2017

Re: Conditional formatting based on multiple conditions

[ Edited ]

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

Regular Visitor
Posts: 47
Registered: ‎02-01-2018

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!

Highlighted
Super User
Posts: 685
Registered: ‎11-01-2017

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

Member
Posts: 140
Registered: ‎01-23-2017

Re: Conditional formatting based on multiple conditions

Member
Posts: 140
Registered: ‎01-23-2017

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...

Super User
Posts: 685
Registered: ‎11-01-2017

Re: Conditional formatting based on multiple conditions

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

 

Thanks

Raj

Member
Posts: 51
Registered: ‎11-06-2015

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

  

 

 

 

 

 

 

 

Member
Posts: 51
Registered: ‎11-06-2015

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