Everyone seesonly their rows.

Row-level security lets one semantic model and one report serve everyone, each person seeing only the rows that are theirs. It lives in the model, not in the report, so every visual, every page and every report on the model obeys it.

#A role

In Power BI Desktop, open Modeling and choose Manage roles. Press New, name the role Austria, pick the Customer table and add a filter on Country. Or switch to the DAX editor and write it yourself:

[Country] = "Austria"

A filter on a role is a DAX expression that is true for every row a member may see. Here that is every customer in Austria. Press Save.

#How the filter flows

The filter is set on Customer, but the numbers live in Sales. It reaches them through the relationship: Customer filters Sales, one to many, the same way a slicer on Country would. So a member of the role sees only Austria's customers, and only their sales.

That flow is also the first thing to check. A table that no relationship reaches from Customer, a target table for example, is not filtered at all.

#See it as the role

Open Modeling, choose View as, tick Austria and press OK. A bar at the top says you are viewing as the role. Add a card with this measure to be sure:

Customers seen = COUNTROWS ( Customer )

With the role on, it counts only Austria's customers, and a table of sales by country shows one row. Press Stop viewing to see everything again.

#Totals follow the role

A measure such as a share of the total is worked out inside what the role may see:

Share of sales =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Customer ) )
)

For the Austria role, REMOVEFILTERS takes away the report's filters, not the role's, so the share is of Austria's sales. Security filters cannot be removed by a measure, which is what makes them security.

A role per country works for three countries. For a sales team where each person sees their own countries, and the list changes, you want one role whose filter reads who may see what from a table.

#A table of who may see what

Make a table with one row per person and country, from a SharePoint list, an Excel file or a table in a database the business keeps up to date:

Email                    Country
anna@contoso.com         Austria
anna@contoso.com         Germany
marc@contoso.com         France

Load it into the model as UserAccess. Do not relate it to any other table, and hide it from the report view: its only job is to be read by the role.

#One filter that reads it

In Manage roles, make a role named Country access, pick the Customer table and write this filter in the DAX editor:

[Country]
    IN CALCULATETABLE (
        VALUES ( UserAccess[Country] ),
        UserAccess[Email] = USERPRINCIPALNAME ()
    )

USERPRINCIPALNAME is the email address of whoever is reading the report. The inner part finds that person's countries in UserAccess, and the filter keeps every customer in one of them. Anna sees Austria and Germany, Marc sees France, and someone with no row sees nothing.

#Why no relationship

A relationship from UserAccess to Customer would need to filter both ways and pass the security filter across, a setting that is easy to forget and slows bigger models. Reading the table inside the filter keeps the model's relationships exactly as they were, one direction each.

#Test it as someone else

Open View as, tick Other user, type anna@contoso.com, tick the role Country access and press OK. The report now shows Austria and Germany only. Try Marc, and then an address that is not in the table, which should show an empty report.

A card with the signed-in name helps while testing:

Signed in as = USERPRINCIPALNAME ()

Remove it before the report is published.

#Keep the table right

The role is only as good as the table. Load the addresses in lower case and compare them that way, so a capital letter in the list does not lock someone out:

[Country]
    IN CALCULATETABLE (
        VALUES ( UserAccess[Country] ),
        UserAccess[Email] = LOWER ( USERPRINCIPALNAME () )
    )

Roles made in Power BI Desktop do nothing until people are put in them. That happens in the Power BI service, after publishing, and it is also where you test the role as the people who will use it.

#Publish

Press Publish in Power BI Desktop and pick the workspace. The semantic model and the report go up together, with the role and its filter inside the model.

#Put people in the role

In the workspace, open the semantic model's menu (the three dots) and choose Security. Pick the role Country access and add members. Add a security group rather than people one by one: whoever joins the group gets the role, and whoever leaves loses it, without anyone touching the model.

With one role for everyone, the whole sales team goes into that one role. What each person sees still comes from the UserAccess table.

#Who it applies to

Row-level security filters the report for people who read it: members of the workspace with the Viewer role, and people who get it through an app or a share. It does not filter Admin, Member or Contributor in the workspace. They can edit the model, so they see all of it.

That makes the workspace roles part of the security. Give the people who only read reports the Viewer role, or share an app with them, and keep editing rights for those who build.

#Test as a user

Still under Security, open the role's menu and choose Test as role. The report opens as the role; at the top, Now viewing as lets you type another person's email address and see the report exactly as they will. Check three people:

• someone with one country,
• someone with several,
• someone with none, who should see an empty report.

#Refresh keeps it current

The UserAccess table refreshes with the rest of the model. Schedule the refresh under the semantic model's Settings, and a new row in the list becomes access the next morning.

#What you built

A role with a fixed filter, one role for everyone that reads who may see what from a table, tested as other people in Power BI Desktop and in the service, and given to a group, so access changes without changing the model.