Hello everyone,
I’m aiming to implement conditional formatting in a Power BI matrix visualization by Comparing Values from two different tables. I’ve successfully created the matrix, but I’m encountering challenges with the conditional formatting aspect, specifically when it involves cross-table value comparison.
Let me detail my setup. I have two primary tables: ‘Services’ and ‘Goals’.
The ‘Services’ table tracks services provided by employees. It includes columns for ‘ID’ (Employee ID), ‘Encounter ID’ (unique service event identifier – multiple services can occur per encounter), and ‘YearMonth’ (date normalized to the first day of the month). Here’s a sample:
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 |
The second table, ‘Goals’, outlines the monthly encounter goals for each employee:
| ID | Goal |
|---|---|
| 123 | 2 |
| 456 | 3 |
| 789 | Ineligible |
My objective is to count the distinct encounters for each employee per month. Then, I need to conditionally format the matrix cells to highlight those where the distinct encounter count exceeds the set goal from the ‘Goals’ table. This visualization should clearly show when an employee has surpassed their monthly targets by comparing values. Here’s an example of the desired output:
To achieve this, I’ve started by creating measures. I believe I need two measures: one to calculate the distinct count of encounter IDs per month, and another to compare this count against the goals from the ‘Goals’ table.
I successfully created the first measure to count distinct encounters:
NumberHours = CALCULATE( DISTINCTCOUNT(Services[Encounter ID]), FILTER(Services, Services[YearMonth] ))However, I’m currently stuck on developing the second measure needed to perform the value comparison for conditional formatting:
GoalMet = CALCULATE( if(Services[NumberHours] > BonusGoals[Goals], 1, 0) )With this second measure, I anticipate being able to apply conditional formatting to highlight the matrix cells based on whether the ‘GoalMet’ measure indicates that the goal was exceeded. However, I’m unsure if this is the correct approach, especially when comparing values across these two tables.
Am I on the right path? Is achieving this type of conditional formatting, which involves comparing values from different tables, feasible within Power BI? Any guidance or alternative approaches would be greatly appreciated!
Solved! Go to Solution.
