cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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
Community Support
Community Support

Hi, @pbiguru999 
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


Regular Visitor

anyone?

Hi, @pbiguru999 

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors