Conditional formatting comparing values across different tables

[regnidem]

Hello all,

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:

Spoiler

ID	Encounter ID	YearMonth
123	741	1/1/2018
123	741	1/1/2018
123	842	1/1/2018
123	842	1/1/2018
123	842	1/1/2018
123	842	1/1/2018
123	953	1/1/2018
123	654	2/1/2018
123	789	3/1/2018
123	253	3/1/2018
456	253	1/1/2018
456	753	1/1/2018
456	853	1/1/2018
456	753	1/1/2018
456	157	2/1/2018
456	149	2/1/2018
456	367	2/1/2018
456	954	2/1/2018
456	756	3/1/2018
456	931	3/1/2018
789	584	1/1/2018
789	526	1/1/2018
789	254	1/1/2018
789	256	1/1/2018
789	985	2/1/2018
789	125	2/1/2018
789	325	2/1/2018
789	652	3/1/2018
789	452	3/1/2018
789	458	3/1/2018

I have a second table that lists the monthly goals for number of encounters for each employee

ID	Goal
123	2
456	3
789	Ineligible

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?

[MarkLaf · Accepted solution]

Yes, you are on the right track and basically there.

I think the main issue is that because you have text in your BonusGoals[Goal] column, it can't be recognized as a number column to compare against number of encounters. I would either remove that row or make the goal null, and change format to number, although the below measure should work with your data as is (IFERROR + VALUE are handling the text):

GoalMet = 
CALCULATE(
    VAR CurGoal = IFERROR(VALUE(SELECTEDVALUE(BonusGoals[Goal])),BLANK()) RETURN
    IF([Encounters]>= CurGoal && ISNUMBER(CurGoal),1,0),
    Services
)

P.S. as @Greg_Deckler also mentions, your NumberHours measure can just be DISTINCTCOUNT(Services[Encounter ID])  -- no need for the calculate or filtering, since the dates and encounter IDs are in the same table so relationship between encouters and dates are implicitly known.

[Greg_Deckler]

Get rid of the CALCULATE and probably use LOOKUPVALUE to grab your Goal.