Forum Discussion
Row Level Security - Explain why this DAX using Lookupvalue works for multiple results
Solved
The additional fields included as part of the lookupvalue() dax rule are like an "AND" condition on the lookup itself.
Consider the original formula:
[RegionID] =
LOOKUPVALUE (
UserTablewithRegion[Team],
UserTablewithRegion[Email], USERPRINCIPALNAME(),UserTablewithRegion[Team],Regions[RegionID]
)and let's rewrite it with context:
Return the Team from the UserTablewithRegion Table
WHERE
UserTablewithRegion[Email] = USERPRINCIPALNAME()
AND
UserTablewithRegion[Team] = Region[RegionID]
It would be possible to add more search terms as well. If I remove this second condition - the lookupvalue no longer returns one result so it fails. By including the Team=RegionID at the end - the formula is now able to have 1 result per combination:
Greg_Deckler
I'm not sure if I'm satisfied with that response.
If I remove the last two fields from the measure - it no longer works.
Can you better explain why these two values at the end of the DAX used in the measure are the difference between it working and not working? Does DAX used in RLS run like a calculated column?
swise001 - Yes, it is like a calculated column, hence it has row context. Think of it this way, you create a calculated column that returns true or false as to whether you can see it or not. RLS is kind of it's own thing and I have no idea how they are translating the DAX you input into the actual security, it's kind of black box that way. But, the fact that you can reference columns without aggregation demonstrates that however they are implementing it, it respects row context, which kind of makes sense since it is "row" level security after all.