Row Level Security not applied through LOOKUPVALUE in Default filter [closed]

0 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.

closed with the note: This is a duplicate from Stackoverflow.
Nov 8, 2018 in Power BI by Shubham
• 13,310 points

closed Jul 3 by Vardhan 881 views
0 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.

answered Nov 8, 2018 by Upasana
• 8,550 points

Related Questions In Power BI

0 votes
1 answer

use Power BI's Row Level security to implement employee filter

We also have a similar feature in ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 16,710 points
112 views
0 votes
1 answer

How to add Row level security?

To add Row Level Security, Follow the ...READ MORE

answered Oct 30 in Power BI by ch
• 3,290 points
13 views
0 votes
1 answer

Filter parameters in Power BI

I think this happening because you're using ...READ MORE

answered Oct 3, 2018 in Power BI by Kalgi
• 42,510 points
116 views
0 votes
1 answer

Calculated column with the sum of values from many columns in a row

Hi, You can create a new column by ...READ MORE

answered Mar 14 in Power BI by Cherukuri
• 31,840 points
61 views
+3 votes
2 answers

Combine tables in Power BI

You can also achieve this using a ...READ MORE

answered Oct 5, 2018 in Power BI by lina
• 8,110 points

edited Oct 11, 2018 by Kalgi 218 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 42,510 points
107 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 22 in Power BI by VNK
9,113 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 42,510 points
8,933 views
0 votes
1 answer

Error:connecting to Azure SSAS server in Power-BI through Import mode

If I understand correctly, you're connecting to ...READ MORE

answered Nov 9, 2018 in Power BI by Upasana
• 8,550 points
345 views
0 votes
1 answer

To expand all lists in a row of lists at the same time without recurring values

Can't promise simple, but I have something ...READ MORE

answered Feb 19 in Power BI by Upasana
• 8,550 points
322 views