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

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

This one is also not working!!

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

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

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        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:-