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,490 points

closed Jul 3, 2019 by Vardhan 5,333 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,620 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
• 18,540 points
1,006 views
0 votes
1 answer

By default, which level of filter behaves as same as a slicer in a report in Power BI desktop?

Hey, There are several types of filters in ...READ MORE

answered Jan 31, 2020 in Power BI by Rakesh
5,742 views
0 votes
1 answer

How to add Row level security?

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

answered Oct 30, 2019 in Power BI by ch
• 3,450 points
739 views
0 votes
1 answer

Row Level Security on a Paginated report

RLS is now only available on the ...READ MORE

answered Feb 10, 2022 in Power BI by CoolCoder
• 4,420 points
2,134 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,220 points

edited Oct 11, 2018 by Kalgi 2,377 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
• 52,360 points
1,112 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 23, 2019 in Power BI by VNK
18,843 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
• 52,360 points
37,485 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,620 points
2,611 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, 2019 in Power BI by Upasana
• 8,620 points
6,033 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP