cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jdbusselman Frequent Visitor
Frequent Visitor

RLS vs Filter for current user

Environment:  SQL 2016, using Tabular models, Power BI, live connection with Tabular Models

 

I see a lot of people asking about filtering to "Current User", this is a simple and easy thing to do within SSRS as a hidden parameter that is filtered into the data query.  However, pretty much all of the answers are to do Row Level Security.  There is a very different need between filtering to current user and securing the data. 

 

Problem:  Same as a lot of people I want to create a dashboard/report where it auto filters to the current user, or at least defaults the filter to the current user.

 

Requirement: When a user opens the Power BI dashboard/report it should filter to their stuff.  Single dashboard as this is a corporate dashboard so if things are modified it's done one time and everyone has the same view of their information. 

 

 

I have a working version of Row Level Security on one of our Tabular Models, however this doesn't actually solve my problem.  I don't actually want to secure the data and only let the current user see their stuff.  If I were to implement RLS, then I would need 2 Tabular models on the same data, one to filter for current user and another to let them see everything if they want.  That's not a viable solution.  I found a work-around for date filtering to be current day/month/yr etc, but cannot find anything to do something similar with the current user.  I have used the USERNAME() for the RLS, however that cannot be used in a table column within the tabular model.

 

I'm not sure if having the USERNAME() function work dynamically at query time is on the roadmap of improvements or if anyone has found any other solution to get the report/dashboard to filter or default to the current user.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: RLS vs Filter for current user

@jdbusselman,

 

As far as I know, there is no elegant way. You may leave a comment and vote this idea up.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: RLS vs Filter for current user

@jdbusselman,

 

As far as I know, there is no elegant way. You may leave a comment and vote this idea up.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Sokon Member
Member

Re: RLS vs Filter for current user

@jdbusselman: I found this post while looking for a solution to the very same problem. I couldn't find a solution elsewhere so I tried myself and I think I found a usable workaround to achieve the desired result without having to use RLS.

 

  1. Create a table to use as a filter later on: "Filter Table" with one column, two values: "yes" and "no". I did it with M:
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVorViVbyy1eKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Choice = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Choice", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Choice", "Show only my stuff?"}})
    in
        #"Renamed Columns"
  2. Create a Measure that filters data using username():
    # my stuff = CALCULATE(
        COUNTROWS('AllStuffTable');
        FILTER(
            ALL('AllStuffTable'[UserName]);
            'AllStuffTable'[UserName] = username()
    ) 
  3. Create a Measure that chooses the measure above when the value "yes" is selected in the "Filter Table", otherwise show unfiltered data:
    # stuff = 
    SWITCH (
        SELECTEDVALUE ( 'Filter Table'[Show only my stuff?] );
        "yes"; [# my stuff)];
        COUNTROWS ( 'AllStuffTable')
    )
  4. Create a data table with all desired fields an add the "# stuff" measure. By default, tables don't show rows where all measures are BLANK. We can use that, because for data that is not "my stuff" the measure "# stuff" will be BLANK and therefore excluded from the table.
  5. When filtering the "Filter Table" to "yes" or "no" you will see a filtered result in the data table or not. You could put the filter in the filter pane or as a slicer on the canvas for user to switch to "their stuff" an back.

Does that make sense?

DooDoo Occasional Visitor
Occasional Visitor

Re: RLS vs Filter for current user

@Sokon Do you have a pbi example to share as i do not succeed in implementing your solution ?

 

Thanks

 

Stephane

miguelarce Visitor
Visitor

Re: RLS vs Filter for current user

Measure1
WhoIsWatching = USERPRINCIPALNAME() 
(that is email style usernames, or you can use USERNAME() for windows style users)

Measure 2
FilterByViewer = IF(selectedvalue(table[email])=[WhoIsWatching],1,0)

Drag Measure 2 as a filter for visual, select advanced filtering and set it to 
"Show items when value IS 1"

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors