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

[TomLU123]

Hi Expert, 

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

Table 1 Active Emploee Table

Employee ID	Location	Department
9619	Hong Kong	A
100006008	Hong Kong	B
5184	Hong Kong	C
3115	Shanghai	B
3097	Shanghai	C
10043	Chengdu	B
100031154	Singapore	C
2062	Paris	A
3132	New York	C
2543	New York	C

Table 2 Terminated Employee Table

Employee ID	Location	Department
6666666	New York	A
7777777	Paris	C
8888888	New York	C
9999999	Chengdu	C
1111111	Chengdu	C
2222222	Chengdu	B
3333333	Singapore	A
4444444	Singapore	A
5555555	Hong Kong	C
6555555	Hong Kong	C

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

[v-xicai · Accepted solution]

Hi @TomLU123 ,

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

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

Best Regards,

Amy

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