grouping an applied filter across two tables

[steviehwkns]

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!

[V-pazhen-msft · Accepted solution]

@steviehwkns 

Create the below measure. Check the pbix if needed.

Measure = calculate(SUM('Table'[Tier]),FILTER('Table','Table'[Tier]= MINX(ALL('Table'),[Tier]) || [Tier]=MAXX(ALL('Table'),[Tier])))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[steviehwkns]

Perfect! Thank you

[amitchandak]

@steviehwkns ,

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

[steviehwkns]

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,