Conditional Formatting using DAX

[Anonymous]

Hi,

I'm trying to write a Dax measure for conditional formatting to occur  in the below table as follows - 

• Staff ID's that have a "Yes" in "LSL Accrued >2 Years", blank in "LSL Accrued >3" and if the figure in 'accrued leave (days) is different to the figure in 'Leave booked' show as Orange 

• Staff ID's that have a Yes in both LSL Accrued >2 Years and LSL Accrued >3 Years and have a figure in accrued leave (days) that is different to leave booked show as Red.

Table would look like this - 

Staff ID	LSL Accrued >2	LSL Accrued >3	Leave booked	Accrued Leave
1233456	Yes		0.0	10.0
1324644	Yes	Yes	10.0	10.0
1238751			5.0	10.0
1325482
5484184	Yes		14.0	14.0
484564
128796	Yes	Yes	10.0	12.0
1254844	Yes	Yes	22.0	22.0
1258796	Yes		5.0	20.0
123846	Yes	Yes	20.0	35.0
58974			7.0	14.0
123658	Yes		0.0	25.0

Would really appreciate any help! @Pragati11 

Thank you 🙂

[Anonymous · Accepted solution]

Hi @Anonymous ,

There are some mistakes in VahidDM 's code. According to your statement, I know there should be a same condition that the figure in 'accrued leave (days) is different to the figure in 'Leave booked' in code.

Color Staff ID = 
VAR _LB =
    SUM ( 'Table'[Leave booked] )
VAR _AB =
    SUM ( 'Table'[Accrued Leave] )
VAR _LSL2 =
    MAX ( 'Table'[LSL Accrued >2] )
VAR _LSL3 =
    MAX ( 'Table'[LSL Accrued >3] )
RETURN
    IF (
        _LB <> _AB,
        IF (
            _LSL2 = "Yes"
                && _LSL3 = BLANK (),
            "Orange",
            IF ( _LSL2 = "Yes" && _LSL3 = "Yes", "Red" )
        )
    )

Add this measure in background color and choose Field value. Result is as below.  

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous I've just noticed that there are some strange anomalies occuring with the conditional formatting - the dax measure doesn't appear to be recognising that some of the Accrued LSL values are the same at the Bookings values.  I tried adjusting the measure to Bookings < Accrued but that didn't seem to work either.  

[Anonymous]

Hi @Anonymous ,

I think the logic of my measure should be correct. Firstly, it will compare 'accrued leave (days) with 'Leave booked'. Only 'accrued leave (days) is different to the figure in 'Leave booked', it will go to next calculate, or the color is blank.

This may caused by your data model. Please share a sample file with us. This will make us easier to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

HI @Anonymous 

Just updated my code, try this:

Color Staff ID = 
VAR _LB =
    MAX ( 'Table'[Leave booked] )
VAR _AB =
    MAX ( 'Table'[Accrued Leave] )
VAR _LSL2 =
    MAX ( 'Table'[LSL Accrued >2] )
VAR _LSL3 =
    MAX ( 'Table'[LSL Accrued >3] )
RETURN
    IF (
        _AB <> _LB
            && _LSL2 = "Yes"
            && _LSL3 = BLANK (),
        "#E66C37",
        IF ( _AB <> _LB && _LSL2 = "Yes" && _LSL3 = "Yes", "#F61223" )
    )

File attached, please download that.

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Thank you, this has worked perfectly! 😻

Is there a way to conduct a count of how many staff ID's meet the conditional formatting ciriteria?

[VahidDM]

Hi @Anonymous 

Try this code and use that in the conditional formatting:

Color Staff ID = 
Var _LB = max('Table'[Leave booked])
Var _AB = max('Table'[Accrued Leave])
Var _LSL2 = max('Table'[LSL Accrued >2])
Var _LSL3 = max('Table'[LSL Accrued >3]) 
return
if(_AB=_LB&&_LSL2="Yes"&&_LSL3=blank(),"#E66C37",if(_AB<>_LB&&_LSL2="Yes"&&_LSL3="Yes","#F61223"))

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/