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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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:

 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

14 REPLIES 14
nmck86
Post Patron
Post Patron

Anonymous
Not applicable

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:

 COndi.Format1.PNG

your result will be like this:

 

Condi.Format2.PNG

 

 

Hope this is what you are looking for.

 

Thanks

Raj

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

This worked great! Thanks for your help.

Hi Rajendran,

 

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

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:
 
Thanks

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

  

 

 

 

 

 

 

 

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

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
Not applicable

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
Not applicable

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

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
Not applicable

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

 

Thanks

Raj

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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