Dynamically filter one table by another without using relationships

[jamesbw]

I have built a relationship structure which I must maintain in order to keep my data model's current functionality:

The current structure works correctly, however what I am now aiming to achieve is to have the "DDL Filter" filtered by a singular slicer through the "A x B" table which in turn filters the "B x C" table, while also being filtered through the current relationships.

This is the DDL Filter table:

This is the A x B table:

And finally the E table, which contains the final output "E Name":

So ideally the dynamic selection made using a slicer in the "A x B" table will filter the "Selection" column in the "DDL Filter" table, in turn filtering the "DDL" column which is connected to the "B x C" table, feeding through to table "E", allowing for the output of "E Name" to be filtered twice: once through the current relationship structure and once by the "DDL Filter" table, all by making one slicer selection of "A x B ID" in the "A x B" table.

I have attempted to use DAX measures and tables such as using the TREATAS function, but I have not had any luck.

I have been able to filter table "E" correctly using two separate slicers (one connected to the A x B table and one connected to DDL Filter table), but this functionality is not efficient enough for the user experience I am attempting to create.

Here is a link to the file, which gives more detail on the nature of the current relationship structure: https://filebin.net/ok8sac9akj0yg1ju 

Any feedback would be appreciated. Thank you!

[V-lianl-msft · Accepted solution]

Hi @jamesbw ,

You can use the query editor to create a merged table as a slicer, and create if measure as a visual level filter.

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

[V-lianl-msft · Accepted solution]

Hi @jamesbw ,

You can use the query editor to create a merged table as a slicer, and create if measure as a visual level filter.

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

[jamesbw · Accepted solution]

Thank you to everyone for your potential solutions, it is much appreciated.

I managed to resolve my problem by connecting the DDL Filter table to the A x B table with a relationship and then set up this measure which I then filtered in a visual table with E Name to only show values which were greater than zero:

CustomFilter = VAR MyFilter = VALUES('DDL Filter'[Filter])

RETURN

SUMX('B X C', IF('B X C'[DDL] IN MyFilter, 1, 0))

[amitchandak]

@jamesbw , If table are independent table

you can try  filter like

calculate(countrows('A x B'), filter('A x B', 'A x B'[A x B ID] in values( E[ID])) )