Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 2

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

In the previous post, you learned about the challenge of security when Implementing it using multiple positions scenario combined with organizational hierarchy. We started to implement the solution using DAX in row level security. This post is the second part of the solution.

Prerequisite

The sample dataset for this example can be downloaded from here.

The first part of this article is here.

Good reads related to this article

I have written several articles about Row Level Security in Power BI, I advise you to have a good read on articles below;

Row Level Security in Power BI

Dynamic Row Level Security

Dynamic Row Level Security with Manager Level Access

Dynamic Row Level Security with Users and Profiles

Dynamic Row Level Security with Organizational Hierarchy

Row Level Security with Analysis Services Live Connection

Continuing the Solution

Expanding the Hierarchy

We need to expand the hierarchy of the organization to be able to search a user through it. We can use Path() DAX function for that. Below is a calculated column added to the Organization table;

Path function accepts two parameters; The ID column (ID column in the Organization table), and the parent ID column (Manager ID). as a result, we will have a string of values separated by “|” showing the entire hierarchy for that member of the organization.

Looking for the User Organization ID in the Hierarchy of Organization

Now that we know what are the organization IDs associated with the user, and also the hierarchy of the organization, we have to search in every row to find out and see if they match somewhere.

Implementing the Loop

Because the whole process should be dynamic based on the logged in user, we cannot do that as a calculated column in the Organization table, we should do that in our measure calculation (continuing calculation from the previous part of this article);

As you can see, I removed the ConcatenateX because it was just for showing a view of the data in the table and did the CrossJoin() of the two tables. CrossJoin gets the name of tables and creates a cartesian product of rows in both tables. For each row in the Organization table, we will have all rows coming from the result of the Filter function (which is organizations associated with the current user).

I just put the expression above inside another ConcatenateX to show the values in there:

Checking if the Path contains the current user’s organization ID

Now that we have created the loop structure using CrossJoin function, we can add a column to that virtual table and see if the path column in each row contains the current user’s organization ID or not, we can do that using AddColumn() function in DAX;

AddColumns will add a column to the existing table, using three parameters; The table to add the column to it (The output table from CrossJoin step), Name of the New Column (“Path Contains This Organization”), and the expression for this column (PathContains). PathContains() is a DAX function that checks if a path does contain a value in it or not. I have written about Path and parent-child functions in a separate article, which I encourage you to read here.

The output of the expression above is still a table, and cannot be visualized. However, if you use another ConcatenateX to see the output table, this is how it looks like;

The output of the above expression looks like below;

All of the rows that show TRUE are the rows that this user is somewhere in the organization path of it, so as a result, we have to only filter these rows.

Filtering only rows that this user is part of the organization hierarchy of those

Finally, let’s filter only rows with True in their calculated column, which we filtered above. we can do that easily with a FILTER() function;

The output would be similar to this (I used ConcatenateX for it);

Now from the output above, we want to fetch a distinct list of Organization[ID] columns, and let’s say the distinct output of that.

Distinct list of Oraganization IDs

As the last step of this DAX expression, I am using SelectColumns() to only select the Organization ID column from the table output from the previous step, and then getting a Distinct() output of that;

The output of the expression above (using ConcatenateX) looks like this:

Set the Role

Now; the hard part is done! we are ready to set the role. All we need to do is to filter the Organization Table that the Organization ID column in that table is in one of the values output from the expression above. I start this with going to Manage Roles under the Modeling Section;

The whole row-level-security expression is here:

Testing the solution

Now, let’s test the solution and see the output. As you can see below; I have added a few other measures to show the current user’s name, and also current positions;

As you can see in the above view, Reza is CFO and will see everything except information about the CEO and COO.

Now, if I make Reza a CEO (that is how easy it is to become a CEO in our organization ;), and also COO, he will see everything;

Because of the row-level-security defined on the Organization table, the User table, and User Organization table won’t be filtered when the user logs in, You can hide the User Organization table, and then use a measure like this for the current user:

and a measure like this for all the current positions:

These two measures are used in the below report as you can see;

Summary

Implementing row-level-security can be challenging in scenarios such as what you have seen in this article. You have seen a combination of organizational hierarchy and the many-to-many relationship brings some challenges in the implementation. However, using DAX you can solve all the challenges. The expression I mentioned here used some functions such as Filter, SelectColumns, Distinct, CrossJoin, LookupValue, AddColumns and etc. There are many other methods of writing the rule in DAX. If you like to learn more about Row-level security in Power BI, I encourage you to read more here:

Row Level Security in Power BI

Dynamic Row Level Security

Dynamic Row Level Security with Manager Level Access

Dynamic Row Level Security with Users and Profiles

Dynamic Row Level Security with Organizational Hierarchy

Row Level Security with Analysis Services Live Connection

Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 1

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for eight continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://www.radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

6 Comments

  • Your CROSSJOIN has 16 rows, then you add the column “Path Contains This Organization” and only 8 rows are left …

    … because you switched identity from Leila to Reza.

  • These two articles have been a huge help in understanding what’s required if my university must internalize RLS within Power BI. We are currently using PBIRS with live connections to use the custom RLS we have in our views. But, we’re having issues due to our infrastructure security. Long story…
    Anyway, one solution that Microsoft is proposing is for us to go to the PBI Service. When I look at Power BI service security options, it says RLS has moved to Power BI Desktop. If so, is the RLS your describing our only option? Would it work for 1700+ organizations with about 6 levels and up to 10,000 employees?

    • Hi Michael.
      If you are just starting with RLS, this is not the best article to start with. Here some advanced topics are discussed.
      Yes, you would need to implement RLS in Desktop. However, for simple organizational hierarchy scenario, you can follow a much simpler approach like this. This article is talking about a scenario when M2M and organizational hierarchy are needed together. It would be MUCH simpler when it is just one of them.
      Cheers
      Reza

      • Thanks.

        Ours actually is a many-to-many situation. So, your timing is excellent. The additional challenge for us is that our users would like to be able to get to the transaction level, which is about 36 million records and 20 columns minimum per year. When we’ve used the cartesian product within a different product, it has choked. Technically, it might be understandable that it crashed. Functionally, less so.

        • I see. So this should be a good answer to that.
          However, how is the performance? how big is your organization table for one person for example? because that makes the result of crossjoin each time

          Cheers
          Reza

Leave a Reply

Your email address will not be published. Required fields are marked *