cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

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

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

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

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

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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!.

Top Solution Authors
Top Kudoed Authors