Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |