RLS in a Composite model with aggregations Returning Wrong results

[mkizito]

Hello,

I am currently stuck with an RLS issue. My model is a composite one where the fact is DQ, dim tables are dual and aggregation tables are import. My RLS scenario is a hierarchy one where there is a parent and child relationship i.e security group Id 2 can access both group 5 and 7 etc. Given this scenario, I have an RLS expression on a security group table that uses PATHCONTAINS as referenced from https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multipl.... This table is on the filtering end of both the fact and the aggregation table. The aggregation table has a column security group id. If I go to manage aggregations and group by security group Id the numbers come wrong when I log in with a UPN. but the report is faster. However, if I do not group by security group Id the numbers are right except the report is slow.

Please help with some insights here.

[DallasBaba]

@mkizito Please send a Pbix file with a sample dataset and a few more explanations.

These will help resolve the issue quickly.

[DallasBaba]

@mkizito You can try the following steps to resolve the issue with the aggregation table:

1 - Create a new table that contains the security group ID and the corresponding child groups.
2 - Use this new table to create a relationship with the aggregation table.
3 - Use the RL expression on the new table instead of the security group table.
4 - Group by the security group ID in the manage aggregations.

Please let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note: 

If this post helps, please consider Accepting it as the solution to help others find it more quickly. 

Best Regards,

Dallas.

[mkizito]

@DallasBaba noted let me try that approach. Also a detail i forgot is the numbers are particularly wrong when I slice and dice with a column that is not in the aggregation table. When I check DAX studio the SQL query generated does not reveal the culprit