cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shwetadalal
Helper II
Helper II

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

Can someone help!!

 

1 ACCEPTED SOLUTION

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

image.png

View solution in original post

7 REPLIES 7
Samarth_18
Solution Sage
Solution Sage

Hi @shwetadalal ,

 

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

Hey @Samarth_18 

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

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

This one is also not working!!

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

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

image.png

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.