Forum Discussion

swise001's avatar
swise001
Continued Contributor
5 years ago
Solved

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 wor...
Need Help
  • swise001's avatar
    swise001
    5 years ago

    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's avatar
swise001
Continued Contributor
5 years ago

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: