Forum Discussion

ashmitp869's avatar
ashmitp869
Responsive Resident
2 years ago
Solved

Help needed in dax expression - for Conditional Formating for background color.

Hi All,   Help needed for  Conditional Formating for background color.   My requirement :   =if(Len([PTD - Revenue Accrual (Ex-GST)])>0 and [PTD - Revenue Accrual (Ex-GST)]<>'-', if([PTD - Reve...
Need Help
  • ashmitp869's avatar
    ashmitp869
    2 years ago

    Thanks for the help..

    PTD - Revenue Accrual Color =  
SWITCH(
        TRUE(),
        AND(
            LEN([PTD - Revenue Accrual(Ex-GST)]) > 0, 
            [PTD - Revenue Accrual(Ex-GST)] <> BLANK()
        ),      IF(
            [PTD - Revenue Accrual(Ex-GST)] > ([MTD Total - Revenue(Ex-GST)] * 0.3), 
           "#FFC7CE"  // This is a hex color code for a shade of red
        ),
        BLANK()
    )

    instead of "-" I used Blank() 

Irwan's avatar
Irwan
Super User
2 years ago

hello ashmitp869 

 

i suspect because of that RETURN, no?

Have you tried deleting that RETURN command? let see if your DAX still not working even after deleting RETURN.


thank you.

ashmitp869's avatar
ashmitp869
Responsive Resident
2 years ago

I remove the RETURN but still not working

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    hello ashmitp869 

     

     

    i dont know what your dataset looks like, but here are what i found: 

    1. Line 6 shows LEN which is used for string length, is [PTD - Revenue Accrual(Ex-GST)] a string or number?

    2. i assumed [PTD - Revenue Accrual(Ex-GST)] is a string since you are comparing to string value in Line 7.

    3. however, in Line 10, you are comparing [PTD - Revenue Accrual(Ex-GST)] and [MTD Total - Revenue(Ex-GST)] as number format (integer)

    i tried to replicate your data, as you can see the error says you can not compare integer and text together.

     

    if this doesnt solve your issue, please provide your sample data (removing all confidential information).

     

    Hope this will help.

    Thank you.

    • ashmitp869's avatar
      ashmitp869
      Responsive Resident
      2 years ago

      Thanks for the help..

      PTD - Revenue Accrual Color =  
SWITCH(
        TRUE(),
        AND(
            LEN([PTD - Revenue Accrual(Ex-GST)]) > 0, 
            [PTD - Revenue Accrual(Ex-GST)] <> BLANK()
        ),      IF(
            [PTD - Revenue Accrual(Ex-GST)] > ([MTD Total - Revenue(Ex-GST)] * 0.3), 
           "#FFC7CE"  // This is a hex color code for a shade of red
        ),
        BLANK()
    )

      instead of "-" I used Blank() 

      • Irwan's avatar
        Irwan
        Super User
        2 years ago

        glad it is solved.