Filter applied on a Table is not taking effect on another table with relationship 'Many to One'

[Jyothishree]

Hello, We Filtered out some ShopIDs from Shops Table (Screenshot)   as it is has stopped working from few months, say ID 4 and 5. We have got  several measures in the dataset for Employee count and many different criteria reporting of Employees and Administrators. Recently we could see that the ID’s filtered out in Shops Table is not taken effect in Employees Table and as the measures are just counting the Total number of Emplyoyees(Same rule is implemented in many measures), it counts the Employees from the Shop Id which is filtered as well. The Relationship between Employees Table to Shops Table is Many to One (Join on ShopID) and the crossfilter direction was single. I tried making it ‘Both’ but still the filter doesn’t apply on ‘Employees Table’. I am not able to use ‘Apply security filter in both directions as this warning pops up. Is there any simple way to achieve any filter changes applied on Shops Table  must take effect on ‘Employees Table as well, means those ID’s should get filtered out automatically from ‘Employees Table’. I understand adding a table in between with ShopID column and connecting the Id columns from bot table to this table would certainly solve the issue but as I mentioned it isa huge dataset and we have very less time to analyse the impact.

Any help is appreciated.

[v-yangliu-msft · Accepted solution]

Hi  @Jyothishree ,

As far as I know, using bridged tables is the quickest way to encounter this situation

If there are multiple relationships between these tables and the active table doesn't allow the filter to propagate as desired, you might consider using the Related() or Userelationship() functions.

RELATED function (DAX) - DAX | Microsoft Learn

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Ensure that the relationship between the Shop table and the Employee table is set up correctly to allow filtering as expected. Sometimes the presence of inactive relationships or complex model structures can prevent the expected filter propagation.

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

This is the related document, you can view this content:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-bidirectional-filtering

https://community.fabric.microsoft.com/t5/Desktop/Row-Level-Security-Error/td-p/2249910

https://community.fabric.microsoft.com/t5/Desktop/Row-Level-Security-Unable-to-add-roles-because-of-...

Best Regards,

Liu Yang

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