Role-Based Default Filter Context for Power BI

Data Analysis Expressions (DAX), Power BI, Power BI Desktop, SQL Server Analysis Services (SSAS) 4 comments

Update (9/26/2017):

An improved approach to dynamic (user role based) default filter context for Power BI is detailed in Chapter 8 of the Microsoft Power BI Cookbook.

Hi,

The attached technical document describes an approach for providing users of Power BI, SSAS Tabular, and Power Pivot models with a  default filter context. Essentially, DAX metrics are filtered based on characteristics of the user accessing the report and the filter selections applied (‘filter context’). The methodology also supports self-service user filtering following the initial default filter context.

Role-Based Default Filter Context for Power BI

Feel welcome to add comments or questions. My next post will likely focus on the new Tabular Model Scripting Language for SSAS 2016.

4 comments

    1. Thanks Imke. Originally the plan was to provide users with instructions and training on personalizing content packs but these became onerous given the quantity of reports and pages involved. Additionally, since further revisions were expected, users would have to manually personalize again so the methodology in the document has been well received. Like many BI projects the complexity isn’t in the code or design itself but rather communicating the implications to the different stakeholders (e.g. the user table and rules to apply, not mixing role-based metrics with standard metrics in the reports, etc).

      Thanks also for the link and I certainly agree there’s a use case for USERNAME being a part of M. (Per my Wishlist blog post I’d like to see M/Power Query built into SQL Server BI tools as was originally planned and to allow PBIX files to be upgraded to SSAS projects like we’ve had with Power Pivot). Thanks again – I might be visiting Europe and the UK specifically later this year and I’ll post here if this is confirmed.

      Like

      Reply

  2. Hi – thanks for this.
    I am trying to achieve something similar to build a My Work page in a report that defaults the Assignee Slicer to the logged in user, but also allows the user to select another users work.
    Research tells me I should be using the USERPRINCIPALNAME for this.
    To be honest, I am struggling to understand how to implement your process.
    Is there anyway you could provide a few steps?
    Thanks,
    Donal

    Like

    Reply

  3. Thanks for this.
    I am trying to create a simple My Work report that shows a bunch of tasks assigned to users.
    I would like when a logged in user visits the report, the Assignee slicer on the report will auto-select to the logged in user, but also allow the selecting of another user, and your paper seems to suggest this is possible.
    I think I should use the USERPRINCIPALNAME () function instead of USERNAME() as the user email address is common to the the Tasks and Users tables.
    Do you have any pointers as to how to achieve this? – just a few steps!

    Thanks,
    Donal

    Like

    Reply

Leave a Reply