Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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/
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |