Getting Started: Implementing Dynamic Security with row filters in Tabular Models

Standard

In this blog post. I’ll help you get started w/ implementing dynamic security with row filters in Tabular Models.

Scenario:

We’ve users that connect to a Tabular Model via Excel for Data Analysis purposes. One of the analysis that they do is Countries VS. Total Margin:

tabular models countries total margin profit

What we want to do is restrict someone from Europe to see data only about France, Germany and United Kingdom

Solution:

1) Open Tabular Model in SSDT (SQL Server Data Tools)

2) Toolbar > Model > Roles

tabular models BISM roles

3)  Role Manager > NEW > change Name to Europe and Permissions to Read

4) Under the Row Filters, for the Geography Table, enter the following code:

=[Country Region Name]=”France” ||  [Country Region Name]=”Germany” || [Country Region Name]=”United Kingdom”

How to edit code for your scenario? change the [country region name] to your column(s) and also change the values

role tabular dax filter ssdt code

5) Click OK

6) Now let’s test it!

7) Toolbar > Model > Analyze in Excel

8) Select the role Europe

dynamic row filter in tabular models9) Click ok.

10) From Pivot Table, Select Margin & Countries:

DAX tabular models dynamic row filters based on location

11) As you can see, since the role Europe was selected for testing purpose in step 8 –  ONLY France, Germany and UK data is shown in our test! This means that our row filters are working perfectly!

I hope this tutorial helps you get started on implementing dynamic security in Tabular models.

Resource:

WhitePaper: Securing the Tabular BI Semantic Model

 

0 thoughts on “Getting Started: Implementing Dynamic Security with row filters in Tabular Models

  1. Manikant

    Thanks for sharing this great information,
    can we give the access control only on dimension level not on aggregate level?

    Is it possible?

    for example:
    I would like to view the grand total for all countries and view the data for specific country.

What do you think? Leave a comment below.