Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
steviehwkns
Frequent Visitor

grouping an applied filter across two tables

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

BenchmarkingIDGrade TierMinMax
AUSAA11020
AUSAA21015
AUSAA31520
AUSBB13040
AUSBB23035
AUSBB33540

Table 2

BenchmarkingIDEmployeeGradeFTE Pay
AUSA1A11
AUSA2A18
AUSA3B32



Thank you!

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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])))

Vpazhenmsft_0-1628232331438.png

 

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

 

View solution in original post

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@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])))

Vpazhenmsft_0-1628232331438.png

 

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

amitchandak
Super User
Super User

@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

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.

EmployeeGradeFTE PayTier
1A111
1A112
2A181
2A183
3B321
3B322

Thank you,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.