Conditional formatting using dax

[Anonymous]

I want Grey backgroung color where the matrix values are blank. 

For that I wrote following measure but I am not getting desired output.

format= SWITCH(True(),SELECTEDVALUE('Employee Data'[Date])=TODAY(),"Yellow",SELECTEDVALUE('Employee Data'[Actual Hours])<4.5 && SELECTEDVALUE('Employee Data'[Actual Hours])>=4,"LightBlue",SELECTEDVALUE('Employee Data'[Actual Hours])=BLANK(),"Grey")

Matrix:

Can someone help!!

[Samarth_18]

Hi @Anonymous ,

Can you try below code:-

format =
IF (
    MAX ( 'Employee Data'[Date] ) = TODAY (),
    "Yellow",
    IF (
        MAX ( 'Employee Data'[Actual Hours] ) < 4.5
            && MAX ( 'Employee Data'[Actual Hours] ) >= 4,
        "LightBlue",
        IF ( MAX ( 'Employee Data'[Actual Hours] ) = BLANK (), "Grey" )
    )
)

Thanks,

Samarth

[Anonymous]

Hey @Samarth_18 

I tried that code but its not working for blank values!!

[Samarth_18]

can you try below code:-

format =
VAR selected_date =
    MAX ( 'Employee Data'[Date] )
RETURN
    IF (
        ISBLANK ( selected_date ),
        "Grey",
        IF (
            selected_date = TODAY (),
            "Yellow",
            IF ( selected_date < 4.5 && selected_date >= 4, "LightBlue" )
        )
    )

[Anonymous]

This one is also not working!!

[Samarth_18]

Is it possible for you to share PBIX file after removing confidential data?

[Anonymous]

https://inkeysolutions0-my.sharepoint.com/:u:/g/personal/shweta_dalal_inkeysolutions_com/EUji0PMUhix...

[Samarth_18 · Accepted solution]

@Anonymous Actually matrix visual not consider blank value for conditional formating when we use Format by as field value . So we need to do one work around.

1. Update your Actual date column code with below code:-

Actual Hours =
VAR result =
    IF ( WEEKDAY ( 'Employee Data'[Date], 2 ) <= 5, 'Employee Data'[Hours Worked] )
RETURN
    IF ( result = BLANK (), 0, result )

2. Update your conditional formatting code with below code:-

Conditional Formatting = 
IF(
    SELECTEDVALUE('Employee Data'[Date])=TODAY(),
    "#E7F508",
    IF(
        SELECTEDVALUE('Employee Data'[Actual Hours])<4.5 && SELECTEDVALUE('Employee Data'[Actual Hours])>=4,
        "#08BCF5",
        IF(
            SELECTEDVALUE('Employee Data'[Actual Hours]) = 0,
        "#859CA4"
        )
    )
)

You will see below output:-