Appendix: Author a Power BI report filter to use row-level security

 

Power BI reports need to be authored in such a way that it they will successfully filter of data using row level security.

Several changes may need to be made to a report for it to work properly. This includes:

  1. Creating one or more roles in a report
  2. Creating a table filter DAX expression in a role's table, to filter the data that is seen when the report is rendered

A brief walkthrough of this process is described next.

 

Downloading a report as a PBIX file

When a report has already been published to Power BI, it can be downloaded using the following steps:

  1. Navigate to the reports tab in the appropriate workspace

    screenshot

  2. Open the report by clicking on the name

    screenshot

  3. Download the report from the file menu

    screenshot

 

Opening a PBIX report

  1. Open Power BI Desktop
  2. Use the File > Open menu

    screenshot

  3. Locate the file and click open

    screenshot

Creating a role

  1. Open the modelling tab and click on Manage Roles

    screenshot

  2. Create one or more roles

    screenshot

 

Creating a DAX filter expression

  1. Select the role (in this example, the Contact role), then select the table which contains the uniquely identifying information for the individual viewing the report.

    screenshot

  2. Enter a DAX expression that compares a column value to the DAX function USERNAME(). The USERNAME() function returns the parameter that is supplied by Connect 365 to Power Bi.

    In this example, the "contactid" column name contains the uniquely identifying information and it will be compared with the dynamically supplied username value configured within and supplied by Connect 365

    screenshot