What I'm trying to accomplish:
Essentially I'm trying to create a matrix visualization with conditional formatting for the cells. However, it involves comparing values across different tables. Creating the matrix is easy; I've already done that. It's the conditional formatting that I'm having trouble with.
I have two tables. A 'Services' table that has a employee ID, an enounter ID (more than one service can be performed per encounter) and a date (everything has been normalized to the first day of the month). See spoiler:SpoilerIDEncounter IDYearMonth1237411/1/20181237411/1/20181238421/1/20181238421/1/20181238421/1/20181238421/1/20181239531/1/20181236542/1/20181237893/1/20181232533/1/20184562531/1/20184567531/1/20184568531/1/20184567531/1/20184561572/1/20184561492/1/20184563672/1/20184569542/1/20184567563/1/20184569313/1/20187895841/1/20187895261/1/20187892541/1/20187892561/1/20187899852/1/20187891252/1/20187893252/1/20187896523/1/20187894523/1/20187894583/1/2018
I have a second table that lists the monthly goals for number of encounters for each employeeIDGoal12324563789Ineligible
What I need to do is count the number of distinct encounters per employee per month. Then, if the count of distinct encounters is higher than the goal, highlight that cell. An approximate visualization using the above data is shown.
What I've done so far:
While looking at other threads trying to cobble together a solution, I think I need to create two new measures. The first counting the number of distinct encounter IDs per month and the second comparing that count to the goals for each employee.
I was able to create the first measure:NumberHours = CALCULATE( DISTINCTCOUNT(Services[Encounter ID]), FILTER(Services, Services[YearMonth] ))
I'm stuck on the second measure:GoalMet = CALCULATE( if(Services[NumberHours] > BonusGoals[Goals], 1, 0) )
Once I have this measure, I think I can make a conditional formatting statement using the GoalMet measure to highlight the cells, but I'm not positive.
Am I on the right track? Is this something that's possible to accomplish in Power BI?