Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
As far as I know, there is no elegant way. You may leave a comment and vote this idea up.
@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.
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"
# my stuff = CALCULATE( COUNTROWS('AllStuffTable'); FILTER( ALL('AllStuffTable'[UserName]); 'AllStuffTable'[UserName] = username() )
# stuff = SWITCH ( SELECTEDVALUE ( 'Filter Table'[Show only my stuff?] ); "yes"; [# my stuff)]; COUNTROWS ( 'AllStuffTable') )
Does that make sense?
@Sokon Do you have a pbi example to share as i do not succeed in implementing your solution ?
Thanks
Stephane
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"
As far as I know, there is no elegant way. You may leave a comment and vote this idea up.
Yet again the solution is there is no solution, please upvote. Well done all round clap clap
Could I suggest having another status of Unresolvable? It saves me having to read through all the comments before I realise I'm completely wastiung my time
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
2 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
9 | |
6 | |
3 | |
3 | |
2 |