Forum Discussion
RLS not working (totally
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.)
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.)