Conditional formatting based on multiple conditions

[Anonymous]

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

Cabin	Seats	Colour
Business	30	Green
Business	10	Orange
Economy	30	Orange
Economy	10	Red

[nmck86]

Hey it looks like you may be able to help with the post below...

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

[Anonymous · Accepted solution]

Hi @Anonymous

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:

 

your result will be like this:

Hope this is what you are looking for.

Thanks

Raj

[Sjuun]

I have a similar situation, but I keep bumping into the following issue:

I want to alert managers for when certain customers have not generated revenue for a while. 
We have the following customer categories:  A, B, and C  - Those are in a specific, different table than the invoices (facturen) table.
I have a measure that measures how many days have past since the customer had it's last invoice. 

This is the code I wrote:

MAXX(Facturen,

   IF(FILTER(Klantgroep, Klantgroep[GroupName]= "A") && [LaatsteFactuurInDagen] > 30, 1,

   IF(FILTER(Klantgroep, Klantgroep[GroupName] = "A") && [LaatsteFactuurInDagen] > 60, 2,

   IF(FILTER(Klantgroep, Klantgroep[GroupName] = "A") && [LaatsteFactuurInDagen] > 90,3,

      IF(FILTER(Klantgroep, Klantgroep[GroupName] = "B") && [LaatsteFactuurInDagen] > 60,1,

      IF(FILTER(Klantgroep, Klantgroep[GroupName] = "B") && [LaatsteFactuurInDagen] > 90, 2,

      IF(FILTER(Klantgroep, Klantgroep[GroupName] = "B") && [LaatsteFactuurInDagen] > 120, 3,

         IF(FILTER(Klantgroep, Klantgroep[GroupName] = "C") && [LaatsteFactuurInDagen] > 90, 1,

         IF(FILTER(Klantgroep, Klantgroep[GroupName] = "C") && [LaatsteFactuurInDagen] > 180, 2,

         BLANK())))))))))

- Facturen means invoice in Dutch
- I need to add the FILTER function, otherwise the IF function cannot find the Klantgroep Table and GroupName column. 
- LaatstefacturenInDagen means LastInvoiceInDays.

I get the following error: 
De expressie verwijst naar meerdere kolommen. Meerdere kolommen kunnen niet worden geconverteerd maar een scalaire waarde.

Translated this means: 
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

What am I doing wrong here?  And is MAXX the richt function for this particular situation as well? 

Kind regards, 


Junior

[kinda_khalaf]

This worked great! Thanks for your help.

[Anonymous]

Hi Rajendran,

How can i get the same on calculated fields. could you please suggest how to proceed.

[tsalsawinny]

Hy, i already trying your instructions, but still cannot get the solution:

NewSLA =

MAXX(ref_sla,

IF(ref_sla[SLA%] <= DATE(2020,01,01), 1,

IF(ref_sla[SLA%] > DATE(2020,01,01),2)))

this link is refer to my explanation about my project:

https://community.powerbi.com/t5/Desktop/Conditional-Formatting-with-two-conditions-in-DATE-range/td... 

Thanks

[a68tbird]

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. 

Am I missing something?

Thanks very much.

Travis

[a68tbird]

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

[FatherTheWizard]

Hi,

I have different countriesin my data and I would like to have different conditional formatting between countries for the same measure/metric. My fact tables are connected to a Team table which has a column Country filled for each team. That means each of the fact table rows can be connected to country information.

How should I modify the aforementioned "Condition measure" to take this into consideration? I would need something like this

If team[country] = Norway then value for red is 1, for yellow 2, for red 3

if team[country] = Germany then value red is 10, for yellow 20, for red 30 

etc.

[Anonymous]

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!

[Anonymous]

Hi @Anonymous

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]

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

[Anonymous]

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

Thanks

Raj

[TomMartens]

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