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

[ashmitp869]

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 - Revenue Accrual (Ex-GST)]>([MTD Total - Revenue (Ex-GST)]*.3),RGB(255,199,206)) )

My expression:

PTD - Revenue Accrual Color = SWITCH( TRUE(), AND( LEN([PTD - Revenue Accrual(Ex-GST)]) > 0, [PTD - Revenue Accrual(Ex-GST)] <> "-" ), 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() )

Help me with the expression . When using RETURN key word its giving me error.

[Irwan]

hello @ashmitp869 

you use RETURN when you are using VAR.

if there is no VAR in prior to, then no need to put RETURN.

Thank you.

[ashmitp869]

Thanks for the explanation @Irwan .

But do you know why my expression is not working ?

[Irwan]

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]

I remove the RETURN but still not working

[Irwan]

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 · Accepted solution]

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]

glad it is solved.