Power BI App Role based row filtering

1
votes

I have set up an on-premises Tabular model using Roles to filter data by rows. When logging on locally and using Excel this works great and filters as intended. Then I use the Power BI Analysis Services Connector as a service bus to access my model in the Power BI App (https://app.powerbi.com). When I log on with the same user account as the Excel scenario my filter does not work and all rows are returned. The user has access to the entire SSAS server, not just the specified models.

I am using Active Directory for user authentication and synch between Azure AD and on-premises AD.

The user I am testing with is not admin.

I have tried to look at the connector settings, but it’s fairly simple and I can’t see what I have done wrong. The connector uses a service account with read and processing user rights.

Why doesn’t Power BI App use the filtering from my tabular model?

1
ssastabularpowerbissas-tabular

1 Answers

0
votes

  1. "The connector uses a service account with read and processing user rights." The Analysis Services Connector service account needs to be an Analysis Services administrator. Otherwise it can't use EffectiveUserName to impersonate the user that's logged into Power BI.

  2. Log onto the server as the user you log into app.powerbi.com with. Then pull up a command prompt and run: Whoami /upn If that doesn't return the exact email address you log into Power BI with that's a problem.

  3. What approach do you take to Tabular model security. Do you have a role with a row filter expression and list individual users as role members? Or AD groups as role members? Or do you do dynamic security using the USERNAME() DAX function? Can you post details?

  4. Can you check the Power BI user is not an Analysis Services administrator since they get to see all the data?

  5. Can you import the Tabular model into SSDT-BI from the server (to ensure the role members are up to date in SSDT-BI) and run this BIDS Helper feature. It will expand out groups and tell you of any membership issues. Maybe your user is in multiple roles?