Dynamic DAX Security Filter Using Lookup Table with Multiple Values

Question

I am applying dynamic security to a SQL Server Tabular model in an education environment. I have a security table with userid's and the associated school that the user should have access to. My DAX filter works fine if a user has one school, but I get a multi-value error ("A table of multiple values was supplied where a single value was expected.") for users that have more than one school. My LOOKUPVALUE() function is causing the problem, and I'm basically trying to determine how I can create a loop to compare Enrollment[SchoolKey] against each SecurityTable[SchoolKey] value for each user. Any thoughts are much appreciated!

=
CONTAINS (
    'Enrollment',
    'Enrollment'[StudentKey], Student[StudentKey],
    'Enrollment'[SchoolKey], LOOKUPVALUE (
        SecurityTable[SchoolKey],
        SecurityTable[UserId], USERNAME ()
    )
)

mmalik mmalik · Accepted Answer · 2017-01-18T10:24:25

I know this answer is late, but you can try this DAX filter on the Enrollment table:

='Enrollment'[SchoolKey]
    = LOOKUPVALUE (
        SecurityTable[SchoolKey],
        SecurityTable[UserId], USERNAME(),
        SecurityTable[SchoolKey], 'Enrollment'[SchoolKey]
    )

Here, the first search condition in LOOKUPVALUE results in all rows (schools) over which the user has access, while the second search condition further reduces the result set to a single row.

You can refer a detailed example from MSDN at https://msdn.microsoft.com/en-us/library/hh479759.aspx

Dynamic DAX Security Filter Using Lookup Table with Multiple Values

1 Answers