Forum Discussion
RLS vs Filter for current user
- 8 years ago
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.
- 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" - Create a Measure that filters data using username():
# my stuff = CALCULATE( COUNTROWS('AllStuffTable'); FILTER( ALL('AllStuffTable'[UserName]); 'AllStuffTable'[UserName] = username() ) - 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') ) - 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.
- 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?
Sokon Do you have a pbi example to share as i do not succeed in implementing your solution ?
Thanks
Stephane
- miguelarce7 years agoRegular Visitor
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"- Anonymous4 years agoNot applicable
Thanks miguelarce - this saved some time 😉