Forum Discussion
RLS not working (totally
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
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.)
3 Replies
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.
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.)
While this works on Import based models, this does not work on Composite models since you cannot use CONTAINS for RLS.
