Forum Discussion

jamesbw's avatar
jamesbw
Regular Visitor
5 years ago
Solved

Dynamically filter one table by another without using relationships

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!

  • jamesbw's avatar
    jamesbw
    4 years ago

    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))

3 Replies

  • 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])) )

    • jamesbw's avatar
      jamesbw
      Regular Visitor

      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))