Current User - MY STUFF.

[saj]

I know I can use RLS but I want to dynamically filter/slice PBIX based on the Current User (USERPRINCIPALNAME/USERNAME).

At the moment many PBIX need several clicks before a user gets at 'stuff' of interest and that prevents growing usage of reports/dashboards.

I found an old post RLS-vs-Filter-for-current-user but it feels very clunky. Does anyone have any ideas about how to achieve this?

Examples include departmental PBIX..... dynamically filter to my department but still have the option to review other departments after I see how my department is performing.

@GuyInACube and everyone....

[saj · Accepted solution]

I've managed to sort out a fairly straightforward approach.

I love it when a plan comes together.....

Set up a ‘mapping table’ to include the email address of the user plus an ‘interest’ column or the ‘thing’ that you want to slice/filter….

You can select single values or multiple values if concatenated with a pipe.

Create a DAX table which becomes the slicer value. It’s a UNION of the distinct values of the column of interest (in this case it's the [team] column from my Data table) and a ’My Stuff’ entry.

In this example my raw data has a Team column and the mapping table maps individuals to teams ‘of interest’

InterestsUnion = UNION(DISTINCT(SELECTCOLUMNS(Data,"interest",Data[team])),{"My Stuff"})

Now you can create a DAX expression which returns a single text value which is a concatenated string containing all the selected values from the slicer.

Return either the selected value(s) OR the interest value from the mapping table.

InterestList = var SelectedValues = CONCATENATEX(Values('InterestsUnion'[interest]),'InterestsUnion'[interest],"|")

var PersonSpecific = LOOKUPVALUE(Map[interest],Map[person],USERPRINCIPALNAME())

return

if(SelectedValues = "My Stuff",PersonSpecific,

    SelectedValues)

Now you can create a DAX [check] measure so you can determine whether to show the row or not.

As the LIST is a concatenated string with a pipe| separator we can use PATHCONTAINS expression. If no slicer value is selected show all rows.

Check = if(PATHCONTAINS([InterestList],min(Data[team])),1,

    if(isblank([InterestList]),1,0)

)

Set a filter on the visual of choice to show records where [check] = 1.

In the example below I am logged on and my 'Teams' of interest are Team A and B.

The dataset contains Team A B and C only.