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

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!

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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

    Hi Rajendran,

     

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

  • kinda_khalaf's avatar
    kinda_khalaf
    Regular Visitor
    3 years ago

    This worked great! Thanks for your help.

  • Sjuun's avatar
    Sjuun
    Advocate II
    2 years ago

    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