• DataZoe

Add filter to show current user items or all items

I ran into a request recently to filter to current user only items but also allow them to see all items. I at first thought of row-level security but I was unable to get that to allow an unfiltered version without duplicating data. So, as it was not a security request, as in it was ok for them to be able to see other user's data, I thought of using a calculation group.


Power BI has DAX that can only be used in a measure, USERPRINCIPALNAME(), which will return the person who is viewing the report's company email (youralias@yourcompany.com).


USERPRINCIPALNAME – DAX Guide

USERPRINCIPALNAME function (DAX) - DAX | Microsoft Docs


We can use this with the calculation groups to build a simple toggle. This can be used as a slicer (shown below) or even in the Filter pane. In this scenario they had multiple reports off the same data model and some they wanted to pre-filter and others they did not, so this worked great.



To set it up you do need Tabular Editor installed, then follow these steps in Power BI Desktop:

  1. External Tools > Tabular Editor (2 or 3)

  2. Model > New Calculation Group

  3. Rename calculation group table to "Filter"

  4. Add a Calculation Item

  5. All = SELECTEDMEASURE()

  6. Add another Calculation Item

  7. Mine only = CALCULATE ( SELECTEDMEASURE (), FILTER ( 'Data', 'Data'[UserPrincipalName] = USERPRINCIPALNAME () ) )

  8. Rename "Name" column to "Filter Options"

  9. Save


What is neat about this approach is you can also build in other logic as well. Such as if they aren't in the table then show all, or filter to show your direct reports and yourself.


You can test it in Power BI Desktop with the Modeling ribbon > View As.



115 views0 comments

Recent Posts

See All