cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Filter dimension table visual when selecting in fact table visual

Hi

As much as I can see, the filtering in table visuals for one-to-many relations works on direction one-to-many, but not in the inverse direction. I'll explain myself.

  • I have two tables (model), a dimension table (or master) and a facts table (or detail), related one-to-many.
  • I have created two table visuals, each one showing the fields of each model table.
  • When I select a row in the table visual for dimensions, only the related rows in the table visuals for facts are showed. That is the normal behaviour of Power Bi.
  • But when I select a row (or rows) in the table visual for facts, the table visuals for dimension is not filtered, showing only the related ones.

Is there any way to achieve this filter 'many-to-one'?

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @Francisco_G_Cal ,

 

you can configure which tables is filtered by which other table with the filter direction:

https://radacad.com/what-is-the-direction-of-relationship-in-power-bi

 

However if you set all relationships to bidirectional you can get results you don't want and a bad performance. Check the article of Nikola Ilic:

https://towardsdatascience.com/killing-me-softly-bi-directional-relationships-in-power-bi-b6b0b9e150...

 

The best way is to filter the visuals by a DAX measure. Check the following video, here it's described in detail:

https://www.youtube.com/watch?v=1yv5srlqgmI

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

View solution in original post

3 REPLIES 3

OMG! That's IT. I've been DAYS struggling with that, tryin a lot of DAX, when this was the PERFECT and SIMPLE solution.

 

And you answered in a couple of minutes!

 

Thanks!!! ^_^ 😀

 

Ah!... I have just watched the video and the article you recommended me and are AMAZING! 👍👍👍

mattkocak
Kudo Collector
Kudo Collector

Go to the Model tab and edit the relationship between the two tables.

 

mattkocak_0-1660122023926.png

 

You should be able to update the cross filter direction for the table relationship to "both", which will accomplish what you want.

 

mattkocak_2-1660122162994.png

selimovd
Super User
Super User

Hey @Francisco_G_Cal ,

 

you can configure which tables is filtered by which other table with the filter direction:

https://radacad.com/what-is-the-direction-of-relationship-in-power-bi

 

However if you set all relationships to bidirectional you can get results you don't want and a bad performance. Check the article of Nikola Ilic:

https://towardsdatascience.com/killing-me-softly-bi-directional-relationships-in-power-bi-b6b0b9e150...

 

The best way is to filter the visuals by a DAX measure. Check the following video, here it's described in detail:

https://www.youtube.com/watch?v=1yv5srlqgmI

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.