RLS not working (totally

[ToddChitt]

I'm working with Power BI August Update and User Accounts in Azure AD, and want to limit access based on login. But it is not all that straight forward. Here's my security model:

UserName GroupName

======= ========

UserA        Group1

UserB        Group1

UserC        Group 2 

GroupName CustomerNumber
======== =======
Group1        CustomerX

Group1        CustomerY

Group2        CustomerZ

 I add these two tables to my model, then relate the first table to the second on GroupName, and the second table to my Customer table on CustomerNumber. Next, I set up a Role in Power BI Desktop model and add the following filter statement under the UserGroup table: [UserName] = USERNAME().

It does the filtering properly (filters the LIST of Customers, with their respective sales), but does not filter the TOTAL from the Sales table that is joined to Customer table. Sample table result:

UserName GroupName CustomerNumber TotalSales

====== ======== =========== ======

User1         Group1        CustomerX        $500

User1         Group1        CustomerY        $300

------------------------------------------------------

Total                                                $12,000,000

 (Note that the $12,000,000 is the sum total for ALL Customers. )

How do I get this to show the proper $800 ?

Thanks in advance

[djpirra]

While this works on Import based models, this does not work on Composite models since you cannot use CONTAINS for RLS.

[ankitpatira]

@ToddChitt I can't seem to replicate your issue. Are you using table visual for the total or have a measure that is calculating total. Also make sure your relationships are active.

[ToddChitt · Accepted solution]

@ankitpatira:

Yes, a table visual was what I was representing there. All Relationships are activem, and I was summing a Calculated Column, not using a Measure.

I was under the impression that if you had a table structure that was totally One-To-Many (Example: One Salesperson to Many Territories; one Territory to many States, one State to many Customers) that all that would be needed was a simple filter on the top level of that structure, such as 'Salesperson'[UserName] = USERNAME(), and that would 'propogate' the security filters all the way down the line.

That may be the case, but for THIS model, there is a Many-to-Many relationship between Users and Customers. And that relationship goes through the GroupCustomer table.

To solve this, I INNER joined the first two data tables shown in my example, perserving only UserName and CustomerNumber, AND taking a DISTINCT:

  SELECT DISTINCT UserName, CustomerNumber

  FROM UserGroup INNER JOIN GroupCustomer

  ON UserGroup.GroupName = GroupCustomer.GroupName

and used that instead of the two tables.

Next, I put a DAX filter expression on Customer as follows:

CONTAINS ( 'SecurityQuery',
                     'SecurityQuery'[UserName],
                    USERNAME(),
                   'SecurityQuery'[CustomerNumber],
                   'Company'[CustomerNumber]
)

(Note: Credit goes to Marco Russo, Alberto Ferrari and Chris Webb from their book Microsoft SQL Server 2012 Analysis Services, The BISM Tabular Model; Chapter 15)

Now, when I 'View As Role' and select the one Security Role and specify a particular User, I see properly filtered list of Customers AND a correct Total for the Calculated Column.

(Side note: User Forums like this are GREAT. They help me solve my issues because more times than not it's only AFTER I have posted my question do I stumble across the answer on my own.)