cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomLU123
Helper III
Helper III

【Seek Help】 How to enable the Two Filter's Dropdown value based on each other?

Hi Expert, 

 

We are building a report to show the headcount information. The report has following 4 tables. 

Table 1 Active Emploee Table

Employee IDLocationDepartment
9619Hong KongA
100006008Hong KongB
5184Hong KongC
3115ShanghaiB
3097ShanghaiC
10043ChengduB
100031154SingaporeC
2062ParisA
3132New YorkC
2543New YorkC

 

Table 2 Terminated Employee Table

Employee IDLocationDepartment
6666666New YorkA
7777777ParisC
8888888New YorkC
9999999ChengduC
1111111ChengduC
2222222ChengduB
3333333SingaporeA
4444444SingaporeA
5555555Hong KongC
6555555Hong KongC

 

Table 3 Location Lists

Location
New York
Paris
Chengdu
Singapore
Hong Kong
Shanghai

 

Table 4 Department List

Department
A
B
C
D
E

 

We have two filters in the report which are Location and Department. (We use the value from Table 3 for the Location Filter, and Table 4 for the Department Filter). It enables us to filter the value in both Table 1 and 2. 

 

The question we seek your help is:

How should we build the relationship between Table 3 and Table 4 to enable its drop down shows the value based on each other?

For example, if we choose "A" in the Department Filter, the dorp down for Location Filter just show A's location which are "Hong Kong", "Paris", “New York" and "Singapore". 

Same for the other way around, if the user choose "Singapore" in the location filter, the drop down for Department Filter should just show Singapore's Department which are "A" and "C".

 

Many thanks!

 

Best regards,

Tom

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @TomLU123 ,

 

You can create new calculated table Table_UNION, then create relationships like picture below.

 

Table_UNION = UNION('Table 1','Table 2')

 

1.png

 

 

 

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

1 REPLY 1
v-xicai
Community Support
Community Support

Hi @TomLU123 ,

 

You can create new calculated table Table_UNION, then create relationships like picture below.

 

Table_UNION = UNION('Table 1','Table 2')

 

1.png

 

 

 

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.