Filter table in Power Query

[amarsale85]

Hi Experts,

I have a language table which is connected to Fact table which is connected to other dimensions in star schema model. 

So when i add a filter on language from language table, it filters the fact table only but doesn't filters all other dimensions to view all attributes in the selected language because the filter direction is single. I can make the filter direction to "Both" for all tables which make the language filter to work but then "Show Items with no data" stops working.

Its like i want the filter direction to work single for all tables but for language i want it to work in Both direction across all tables.

Regards,

Amar Sale

[v-juanli-msft]

Hi @amarsale85 

You could share a simple data example as Greg_Deckler suggested for further analysis.

Best Regards

Maggie

[Greg_Deckler]

That's going to depend on your data model, can you share a picture?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amarsale85]

Apologize that I couldn't explain it better before but here it is.

So below is the original data model which works for language selection. So when i added a filter on Language, say "Russian" for example, all the other attributes like Category, Priority, Status and Location are displayed in Russian language. So the Language filter, filters fact table which filters all other dimensions.

The issue with this design was "Show items with No data" doesn't work as expected. To explain it bit more, If i selected Priority as "Low" and then selected 10 locations (in filter) which don't have a "Low" priority then none of the 10 locations will display even if you select "Show items with No data". 

To resolve this issue, i changed the filter direction to single direction as shown below:

This causes the language filter to stop working. So if i add a filter on language it won't filter other attributes. I really don't know if there is a solution or a workaround to resolve this.

[v-juanli-msft]

Hi @amarsale85 

In many cases, we use dimemsion tables and fact tables as below:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

https://radacad.com/basics-of-modeling-in-power-bi-what-is-a-dimension-table-and-why-say-no-to-a-sin...

We would recommend to use single relationships for better performance.

In your case, you have language key in many dimension tables, if you want to show each dimension items based on the selection of the language key, you could create inactive relationships among these dimension tables and your "language" table.

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.