Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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

    • a68tbird's avatar
      a68tbird
      Resolver II
      7 years ago

      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's avatar
        a68tbird
        Resolver II
        7 years ago

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