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
Anonymous
Not applicable

Filter multiple tables based on condition

Hi,

 

How can I create a view that allows me to select an employee in table1 and then it  shows me the list of available managers in table 2?

For example: Here's the employee's table

Employee NameEmailLevelGroupGroup1Level1LocationFriend
ABC1 L1G1H1M1Loc1ABC7
ABC2 L2G2H2M2Loc2ABC6
ABC3 L3G3H3M3Loc3ABC5
ABC4 L4G4H4M4Loc4ABC4
ABC5 L5G5H5M5Loc5ABC3
ABC6 L6G6H6M6Loc6ABC2
ABC7 L7G7H7M7Loc7ABC1

 

Now when I click on ABC2 row, it should filter the manager table based on following criteria: -

1. Managers "Level" should be greater than the selected employee

2. The "Location" should be the same as of the selected employee

3. Same "Group" but different "Group1"

4. The Manager should not be employee's "Friend"

 

Here's the managers table that should get filtered based on selection above: 

Manager NameEmailLevelGroupGroup1Level1LocationPending meets
ABC1 L1G1H1M1Loc1 
ABC2 L2G2H2M2Loc2 
ABC3 L3G3H3M3Loc3 
ABC4 L4G4H4M4Loc4 
ABC5 L5G5H5M5Loc5 
ABC6 L6G6H6M6Loc6 
ABC7 L7G7H7M7Loc7 

 

Any suggestion how this can be implemented or any workaround?

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

Hi, @Anonymous 
Could you please tell me whether your problem has been solved?
You can try to  create a measure as below:  

Measure = 
var _level=CALCULATE(MAX('Table'[Level]),FILTER('Table','Table'[Employee Name]=SELECTEDVALUE('Table'[Employee Name])))
var _location=CALCULATE(MAX('Table'[Location]),FILTER('Table','Table'[Employee Name]=SELECTEDVALUE('Table'[Employee Name])))
var _group=CALCULATE(MAX('Table'[Group]),FILTER('Table','Table'[Employee Name]=SELECTEDVALUE('Table'[Employee Name])))
var _group1=CALCULATE(MAX('Table'[Group1]),FILTER('Table','Table'[Employee Name]=SELECTEDVALUE('Table'[Employee Name])))
var _friend=CALCULATE(MAX('Table'[Friend]),FILTER('Table','Table'[Employee Name]=SELECTEDVALUE('Table'[Employee Name])))
Return
CALCULATE(MAX('Table (2)'[Manager Name]),FILTER(ALL('Table (2)'),'Table (2)'[Level]>_level&&'Table (2)'[Location]=_location&&'Table (2)'[Group]=_group&&'Table (2)'[Group1]<>_group1&&'Table (2)'[Manager Name]<>_friend))

If it doesn’t meet you requirement ,do you mind create a sample pbix file and list the expected result clearly, so that we can post detailed solution.

 

Best Regards,
Community Support Team _ Eason


Anonymous
Not applicable

anyone?

Hi, @Anonymous 

Sorry,not very clear.

The employee's table you provided and the filtered manage table confused me. 

They appear to be the same table. If possible, please explain it in more detail with specific examples.

Best Regards,
Community Support Team _ Eason

 

 

 

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.