Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
We've set up the following rule for our RLS in SSAS:
dimSalesPerson[SalesRepKey]=Lookup(dimAdSecurityRoles[DW_ID], dimADSecurityRoles[user_principleName], USERNAME(), dimADSecurityRoles[DW_ID], dimSalesPerson[SalesRepKey])
I've double-checked the output of each row and sales ID and user name match up fine. dimADSecurityRoles[user_principleName] outputs domain/username and should match up with USERNAME() as in SSAS which also appears to output the username in that format. (we tried both the domain/username and username@domain.com format. ).
However, we're getting a couldn't retrieve the data for the visual / can't display the visual error. We get the following error when running the SQL profiler in SSAS.
An error was encountered during the evaluation of the row level security expression defined on table 'dimADSecurityRoles'. Error message: A single value for column 'SalesRepKey' in table 'dimSalesPerson' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
The keys in the SalesRepKey column are all unique. And the relationships look like this:
Any ideas on how to fix this?