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

uphill filtering when using two data tables

Hi,

 

Having a problem which may be as much a methodology issue as a technical one.

 

i have a scenario where i use key tables to filter 2 data tables simulateously.

 

[Data table1]*----1[key table]1----*[Data table2]

 

There are many 'key tables'.

 

The problem i am having is i want the key tables to filter based on other key tables selections

For example i wouldnt want to see every Regionin the world in my 'keyRegion' table if i am (in a different filter) only looking at clientX in using the KeyClient table.

 

I know if i had one table i could simply create fitlers from this, but with two tables i cant figure it out.

 

i know with one table i can use a star schema method and cross filtering, but with multiple data tables i cannot do this.

 

im half way to setting up a whole series of key tables that filter each other or using the DAX method here LINK

 

OR..more likely..i may be askign something that should never be an issue and i need to reevaluate from the bottom up.

 

Any advice would be appreciated!

 

Demo schema

demo schema.png

 

2 REPLIES 2
Highlighted
Super User
Super User

Re: uphill filtering when using two data tables

This is effectively a many to many pattern. Have a read of my article here explaining how to push a filter from one data table onto anoher lookup table. The examples are not quite the same, but the principle is. 

 

https://exceleratorbi.com.au/many-many-relationships-dax-explained/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
al4x Frequent Visitor
Frequent Visitor

Re: uphill filtering when using two data tables

Thanks for the link, i have been reading it.

 

Has this scenario come about (in my case) from poor design, or is it something fairly typical?

Im trying to get my head around the above.

 

I can filter using my key tables, it just means the end user has to scroll through lots of fields that arent necessary.

 

i will attempt to work through your article. I  admit i struggle with 'Calculate' but also know i need to get more famililar with it