RLS is filtering data more than expected

[scottnorts]

Im trying to create security roles within PowerBI to facilitate our product category managers, they need to see all product sales for certain product categories.

To do this, im pulling the ID of the product category down the to InvoiceLine table from the ProductMaster table and then creating a role which specifies what product category users with that role can see. Eg, [L1ProdCategory] = "SF"

The relationship between the 2 tables is as follows, InvoiceLine *:1 ProductMaster, joined by the ProductNumber.

My problem is that when i apply the role, it only shows me a fraction of that product category compared to when I filter the data using a slicer or through the data table itself.

Here's an example of 1 particular invoice, the below 3 codes are in the ProductMaster table and are part of the Safety category as per the ProductGroupId

ProductNumber	ProductGroupId
108040:PK:M	SF
109026:YL:L	SF
109026:YL:S	SF

Below are the 3 lines in the InvoiceLine table that relate to this invoice, when my security role is applied, only the bottom 2 roles in bold appear in the report. The top row is somehow filtered out even though it appears to meet the criterea.

DocNumber	OriginDocNumber	ProductNumber	L1ProdCategory
INV10000477	S10001539	108040:PK:M	SF
INV10000477	S10001539	109026:YL:L	SF
INV10000477	S10001539	109026:YL:S	SF

Is there something about the way that RLS filters the data which i dont understand?

Any help is much appreciated.

Cheers

Scott

[brendaz]

Hi Scott,

I figured this problem, you might have the same issue with me. when I was filtering the target table (in your case InvoiceLine table), I noticed there are actually two tables related to this target table. One is the master table I use to filter the target table in dynamic RLS, and there is ANOTHER table that I did not expected to have the relationship with my target table with (but it's by default having this relationship because there is a unique key involved). I noticed the data that was filtered out by the RLS (not filtered by me intentionally), is because in this ANOTHER table, my data was blank. Then I fill the blank data with zero, the RLS works as normal.

Hope this makes sense. If not, we can take a look at your table relationship diagram and it will be more obvious.

[brendaz]

I'm getting this issue as well. the data is all there when no filter. But once using RLS, no matter what is the criteria to filter (e.g. I tried either using region as filter and name as filter), it by default filtered out couple of lines which are not related to the filter. Seems data relationship issue? Hope there is a solution.

[Mo-PHAT]

Hi Scott,

I am getting this issue now too! Did you end up figuing out what it was?

Kirsty

[scottnorts]

Hi Kirsty,

No sorry I couldn't find out the cause.

I ended up creating a separate dataset for my category manager security roles.

[v-jingzhang]

Hi @scottnorts 

Try creating the filter rule on ProductMaster table with [ProductGroupId] = "SF". And notice the cross filter direction of the relationship. If you apply the filter rule on ProductMaster table, the filter direction should points to InvoiceLine.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[scottnorts]

Hi Jing,

Thanks for your response.

Ive taken a look at the pbix you supplied and tried the same in my model however im still having the same results.
At this point im thinking it must be some issue with my data as all my other roles are behaving as expected.

Cheers

Scott

[v-jingzhang]

Hi @scottnorts 

Check if there are other relationships from other tables that may pass unexpected filters to the table. 

Regards,

Jing