Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I wonder if anyone can help with something i am struggling with. I believe this may not be possible, but hope that someone out there with a little more experience than I is able to shed some light.
I have one table which specifies a tier based on a minimum and maximum value, and another table that specifies a specific value (FTE Pay). These tables have a bi-directional many to many relationship.
I am trying to apply a filter that draws in the relevant people/pay to each specific Tier; for example below employee 1 would need to show as Tier 1 and Tier 3 but not Tier 2.
Table 1
BenchmarkingID | Grade | Tier | Min | Max |
AUSA | A | 1 | 10 | 20 |
AUSA | A | 2 | 10 | 15 |
AUSA | A | 3 | 15 | 20 |
AUSB | B | 1 | 30 | 40 |
AUSB | B | 2 | 30 | 35 |
AUSB | B | 3 | 35 | 40 |
Table 2
BenchmarkingID | Employee | Grade | FTE Pay |
AUSA | 1 | A | 11 |
AUSA | 2 | A | 18 |
AUSA | 3 | B | 32 |
Thank you!
Solved! Go to Solution.
Create the below measure. Check the pbix if needed.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Create the below measure. Check the pbix if needed.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Perfect! Thank you
a measure like
calculate(count(Table2[Employee]), filter(values(Table2[BenchmarkingID]) , Min(Table2[FTE Pay]) >= Min(Table1[Min]) && Min(Table2[FTE Pay]) <= Max(Table1[Min]) ))
Can you share sample output
Hi,
Thank you for the above. I've just tried it and it doesn't quite return what i was looking for as it is assigning every tier to every person, so across tier 1, tier 2, and tier 3 it is counting the same number of people whereas it is expected to be different. This is what I'm trying to get the measure to do - so it only assigned those that are relevant to the tier.
Employee | Grade | FTE Pay | Tier |
1 | A | 11 | 1 |
1 | A | 11 | 2 |
2 | A | 18 | 1 |
2 | A | 18 | 3 |
3 | B | 32 | 1 |
3 | B | 32 | 2 |
Thank you,
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |