Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I'm trying to write a Dax measure for conditional formatting to occur in the below table as follows -
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 🙂
Solved! Go to 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.
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 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.
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.
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/
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?
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/
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |