Configure row-level security with the dynamic method
Defining static rules is simple and effective. However, be aware of disadvantages: setting up static rules can involve significant effort to create and set up. It would also require you to update and republish the dataset when new regions or departments are onboarded.
If there are many rules to set up and you anticipate adding new rules in the future, consider creating dynamic rules instead, without the need to continue maintaining it dynamically.
Defining dynamic rules is simple and effective when a model table stores username or email address values. They allow you to enforce a data-driven RLS design. For example, when salespeople are added to, or removed from, the Employees table (or are assigned to different regions), this design approach just works.
As the admin at Tailwind Traders, you want Power BI row-level security to only show sales to the person who made them. In this example, Russel King has made four sales. When viewing your report, Russel should only see the sales that he's responsible for and no other sales. You can configure row-level security exactly the way you configured it previously, with only a single change. Instead of creating four roles, you only need to create one role. The DAX filter for that role would look similar to the following image.
Notice that instead of the fixed string, such as Game or Clothing, this uses a DAX function in the row-level security filter. The userprincipalname() function will compare the email address from the Employees table with the email that the user entered when signing in to Power BI service. If Russel King uses the email address russel@tailwindtraders.com to sign in to Power BI service, the system will compare that value to the email address in the Employees table. Assuming that a relationship has been created between Employees and Sales, Russel will only see his four sales.
There are three specific DAX functions:
USERNAME or USERPRINCIPALNAME – Returns the Power BI authenticated user as a text value.
CUSTOMDATA - Returns the CustomData property passed in the connection string. Non-Power BI reporting tools that connect to the dataset by using a connection string can set this property, like Microsoft Excel.
Note
Be aware that the USERNAME function returns the user in the format of DOMAIN\username when used in Power BI Desktop. However, when used in the Power BI service, it returns the format of the user's User Principal Name (UPN), like username@adventureworks.com. Alternatively, you can use the USERPRINCIPALNAME function, which always returns the user in the user principal name format.