Default filter in Power BI - Row Level Security not applied through LOOKUPVALUE

3
votes

TL;DR: When using LOOKUPVALUE() against a table with Row Level Security, the RLS is not applied and all values are seen

I have a requirement to have a 'default' value (location) picked in a Power BI report, based on the user.

I am reporting against Azure Analysis Services (tabular model 1400)

It appears that the way to implement default values in Power BI is to dynamically rename a value to something static, and pick that static value as a filter.

So

  • user Bob has default location Location1 so when he logs in he should be see his location
  • user Joe has default location Location2 so he should be filtered on this location

The trick being, they can optionally pick another location and see that if they like

The first thing I tried was using USERPRINCIPALNAME() directly on a row level expression but I get

...USERNAME and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in measures or in the AllowedRowsExpression

So next I figured I would apply RLS to a different table and just look that up, as follows:

I have a table loaded from the database called Location that lists all locations.

I have a standalone table called MyLocation, which is a copy of Location. MyLocation is generated using this DAX:

=SUMMARIZE(Location,Location[LocationKey],Location[Location Name])

(note I have also tried a table based on data, not on DAX)

MyLocation also has dynamic row level security applied like this:

=(
  [LocationKey]
  =
  LOOKUPVALUE(
      'Employee[LocationKey],
      'Employee'[UserPrincipalName],
      USERPRINCIPALNAME())
  )

When I look at MyLocation in Power BI I can see the RLS is applied - only one location can be seen. This is in contrast to Location, where I can see all locations (which has no RLS defined)

Next I added a column (row level expression) in Location to go and pick out this 'single' location that is evaluated using row level security:

=LOOKUPVALUE(
    'MyLocation'[Location Name],
    'MyLocation'[LocationKey],
    'Location'[LocationKey]
)

It's matching on LocationKey, but for records that have been removed via RLS, there should be no match.

However when I test this final column, RLS is ignored, and all locations come through.

I can see both tables right in front of me in Power BI:

MyLocation looks like this: (RLS is applied)

Location       
==========================
Location 3      Location 3

Location looks like this: (No RLS applied but why aren't the other locations blank?)

Location       LookupValue            
==========================
Location 1      Location 1
Location 2      Location 2
Location 3      Location 3
Location 4      Location 4
Location 5      Location 5
...........
....

I expect Location to look like this:

Location       LookupValue            
==========================
Location 1      blank
Location 2      blank
Location 3      Location 3
Location 4      blank
Location 5      blank
...........
....

So it appears no matter what trick you use, you really can't use USERPRINCIPALNAME() on a row.

I've also tried defining USERPRINCIPALNAME() as a measure and using that but that also failed (don't recall the error right now but I'll also retry it)

I also tried using a 'standalone parameter' table to switch RLS on and off using SELECTEDVALUE but the filtered value in the parameter table never appears. ISFILTERED always returns false despite that table being filtered.

3
powerbidaxssas-tabularazure-analysis-services
Can you post what your table relationship diagram looks like and how you set up your RLS roles? – Alexis Olson
There are only two tables in the example (Location, MyLocation), and they do not have relationships defined at all. My understanding is that LOOKUPVALUE does not need a relationship defined. There is only one RLS role with one user and that's what I'm using. There is of course an admin user but I'm not using that to test. – Nick.McDermaid
LOOKUPVALUE doesn't need a relationship, but RLS works as a filter on a table and applies only to that table to any it's related to. It isn't going to apply to a table unrelated to the one you defined the filter for. – Alexis Olson
I understand that without a relationship it won't apply to another table. But I don't understand why LOOKUPVALUE is returning to unfiltered rows. If I list the table the rows are correctly filtered by RLS. If I refer to those rows using LOOKUPVALUE from another table they appear. I can't find anything online describing this behaviour. – Nick.McDermaid
I'm struggling to follow the above a bit, it would help if you were clearer on table and column names. – Mike Honey

3 Answers

1
votes

I see what you mean now. LOOKUPVALUE appears to have unfiltered access to your table, bypassing RLS. I'd suggest reporting this to Microsoft as a bug.

In your report, I'd suggest using measures of this form:

Measure =
VAR EmployeeLocation =
        LOOKUPVALUE(Employee[LocationKey],
                    Employee[UserPrincipalName],
                     USERPRINCIPALNAME())
RETURN IF(ISFILTERED(Location[Location]),
           <expression>,
           CALCULATE(<expression>,
               FILTER(Location, Location[LocationKey] = EmployeeLocation)))

That way it should default to calculating values for EmployeeLocation when Location is left unfiltered and will behave normally otherwise.

1
votes

This is working for me so far:

The objective is to have a given user automatically see their own location, but have the option to see other locations.

I tried many things but eventually I just went to creating separate measures on the fact table that refer directly to USERPRINCIPALNAME

i.e. this shows the location total units against the users location and zero against everything else.

    My Location Units:= 
        CALCULATE(
            SUM([Units]),
            FILTER(
                'Location',
                'Location'[LocationKey]=
                LOOKUPVALUE(
                    'Employee'[LocationKey],
                    'Employee'[UserPrincipalName],
                    USERPRINCIPALNAME()
                )
            )
        )

Then I thought: can't I just use this on a dimension?

Is My Location:= 
    CALCULATE(
    IF(ISBLANK(MAX('Location'[LocationKey])),"No","Yes"),
    FILTER(
        'Location',
        'Location'[LocationKey]=
        LOOKUPVALUE(
            'Employee'[LocationKey],
            'Employee'[UserPrincipalName],
            USERPRINCIPALNAME()
        )
    )
)

Even though I need to use this at a row level, it appears to work even though it is a measure.

It uses CALCULATE(<expression>,FILTER(<table>,<expression>))

The FILTER part does the user level filtering

The expression part turns that into YES/NO

And I can use it to filter in Power BI and pick only the current users location if I want.

I suspect all of these expressions ban be written more simply. Feel free to enlighten me

I still don't know why LOOKUPVALUE does not respect RLS. I'd like to know if it does, but I've done something wrong, or if it doesn't.

1
votes

As I understand it, Lookup[LookupValue] is a Column (not a Measure), using your 3rd code snippet: "=LOOKUPVALUE( ..."

The results of Calculated Columns like that are materialized when you Refresh the model. They are not affected by RLS which is applied at query time, just before the Measures are calculated.

This is not specific to the LOOKUPVALUE function.