Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional formatting using dax

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!!

 

DAX
DAX Commands and Tips
DAX Measures
Need Help DAX
  • Samarth_18's avatar
    Samarth_18
    4 years ago

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

7 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

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

      Hey Samarth_18 

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

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        4 years ago

        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" )
        )
    )