Forum Discussion
Slicers Change my Conditional Formatting Results
Thank you in advance!
Background:
- Our training department provides an excel spreadsheet with employee training data.
- My job is to take that spreadsheet and use Power BI provide managers a quick look at their employee performance.
- Employees above 95% on-time completion are considered to have achieved their goal and above 98% on-time completion are considered to have exceeded their goal.
- On-time completion is calculated, values are put in a matrix, and percent on-time is color coded based on the above.
The problem:
- The most important slicer is the one by manager name.
- If all managers are selected, conditional formatting works.
- HOWEVER, if one manager is selected, condtional formatting...breaks?!?!
Here is a screencap of the report and my conditional formatting setup: https://imgur.com/a/ihHJfaS
DAX Statements:
To assign a status per assignment:
=if(isblank(Report[Transcript Completed Date].[Date]),if(date(year(Report[Transcript Due Date]),month(Report[Transcript Due Date]),day(Report[Transcript Due Date]))>=today(),"Open, Not Yet Due","Open, Past-Due"),if(date(year(Report[Transcript Completed Date])-7/24,month(Report[Transcript Completed Date]-7/24),day(Report[Transcript Completed Date]-7/24))>date(year(Report[Transcript Due Date]),month(Report[Transcript Due Date]),day(Report[Transcript Due Date])),"Completed Late","Completed On-Time")))
To calculate the percent on-time:
= if(CALCULATE(counta(Report[Training Status]),Report[Training Status]="Completed On-Time")/(calculate(counta(Report[Training Status]),Report[Training Status]="Open, Past-Due")+CALCULATE(counta(Report[Training Status]),Report[Training Status]="Completed Late")+CALCULATE(counta(Report[Training Status]),Report[Training Status]="Completed On-Time"))=0,0,CALCULATE(counta(Report[Training Status]),Report[Training Status]="Completed On-Time")/(calculate(counta(Report[Training Status]),Report[Training Status]="Open, Past-Due")+CALCULATE(counta(Report[Training Status]),Report[Training Status]="Completed Late")+CALCULATE(counta(Report[Training Status]),Report[Training Status]="Completed On-Time")))