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

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