Forum Discussion
Row Level Security - Explain why this DAX using Lookupvalue works for multiple results
I'm using an overly simple data model where UserTablewithRegion is disconnected (ignore the Users and User_Region tables).
Im using this DAX for a dynamic role;
This absolutely works for this model and will work even when multiple different "Teams" are returned for a single users.
Curiously - this same DAX when enetered in Power BI - as a measure - does not work.
Can someone explain why this DAX works for multiple results when used in an RLS role?
("Team" and "RegionID" are simple letters "A","B","C", )
Thank you!
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:
6 Replies
swise001 - Row context versus no row context. Measures do not have row context and thus you must use an aggregator like SUM, MAX, MIN, AVERAGE, etc. when referencing columns.
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.
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.
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:
