Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

image.png

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

7 REPLIES 7
Samarth_18
Community Champion
Community Champion

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Anonymous
Not applicable

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Anonymous
Not applicable

This one is also not working!!

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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

image.png

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors