RLS not filter on both side direction many to one relation

[wx5558284]

Hello,

I have a "Hierachy" Table with some over lap parent bucket and a "SubNumber" table with Subbucket and Numbers need to sum.

In Hierachy table the Sub='ab' is located both 'Main'='a' and 'Main'='b'.

Due to no many to many relation I created a caculated Table "DistinctSub": 

DistinctSub = SUMMARIZE(Hierachy,Hierachy[Sub])

Set relation as 'Sub' field from "Hierachy" to "DistinctSub" to "SubNumber".

First relation is many to 1 with both side direction and second relation is 1 to many with 1 side direction.

Now I can have a sumed number on 'Main' field.

But When I set a role for 'Main'='a' I found that the "SubNumber table is not filtered. 

I put a slicer on Hierachy[Main] , it only have one value 'a', but select and deselect it can show/hide "SubNumber" table.

Is there a way to use RLS on many to 1 relation?

Thank you!

https://www.dropbox.com/s/96fvkffe9d1tb8l/RLS%20Sample.pbix?dl=0

[GilbertQ]

Hi there I would suggest starting to see if you had to remove the many - many relationship if this would resolve your current RLS issue. I have seen it in the past where there was a lot of time spent trying to get the RLS working, when the underlying issue was with a Many - Many relationship

[DickMcMullen]

Same issue here, and I resolved for my case by keeping our many-to-many relation, but changing Cross filter direction from "Both" to "Single". 

Thanks for the many-to-many tip -- that's what lead me to understanding that it was the two-directional cross-filtering that was at issue. 

[wx5558284]

Thanks, we use relations is because we already have a lot of info and measures on the SubNumber table, without relation we might need to replicate all the measure under the 'Main' condition. If there's no such way to use RLS on many to one relation I'd do so.

[GilbertQ]

Hi there It would appear that in order for it to work, the data model would need to be changed.