Forum Discussion
Conditional formatting comparing values across different tables
- 7 years ago
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.
Get rid of the CALCULATE and probably use LOOKUPVALUE to grab your Goal.