In the last few weeks or months, some people have asked me how dynamic, line-based security can be implemented.

Of course, the whole thing depends on how a user accesses the data. It is safest if the data source handles security and filters the data by user. Of course, the whole thing only works if the data is queried using a DirectQuery method and the user is therefore known when querying. However, when the data is imported, the data source is of course not known to the user, and the security of each report is implemented. If a Power BI semantic model is used, the whole thing is of course implemented depending on the techniques used to retrieve the data. i.e. Even if a paginated report queries the data from the model, security is applied.

In this entry I would like to explain how this is implemented dynamically in Power BI semantic models and explain it step by step:

In this example I have built a simple data model consisting of a fact table and a dimension table that displays the countries. The aim is that you are only allowed to see the countries for which you have been authorized. In some cases you can also see several countries. The assignment is maintained via a third-party system and is entered into it and transferred to Power BI. i.e. There are no roles that explicitly filter a country in which a user is assigned manually. This means that all users are allowed to access the report, but only see the data to which they have been authorized.

Note: the functionality shown here also works on the report server. However, the function that will be used later is not USERPRINCIPALNAME() but USERNAME().

The data model:

When the data is queried, you can see how many rows are assigned to a respective entry from the dimension:

The next step is to create a table that is assigned to the user in the respective country from the dimension. This table can be taken from the respective system, for example.

This table is now linked to the dimensions in the data model:

It is important that mutual filtering is activated in the relationship and that security information is also passed on. This would also work if the security table is connected to the fact table, this would just use more main memory and so this should be done with a dimension table.

If you evaluate this, it looks like this:

A simple role is then implemented that filters the security table:

If you now test the role, you will only receive the data for the registered user: