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:
swise001 , In case you creating a column, this should work. If you are creating a measure you need to use some aggregation or functions like sumx, countx etc
The DAX is for creating a rule in my Row Level Security Role. This is at the heart of my question.