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

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 = PATH(
    Organization[ID],
    Organization[Manager ID]
    )

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);

Measure = 
CROSSJOIN(
    FILTER(
            'Users Organizations',
            'Users Organizations'[User ID]=
                LOOKUPVALUE(
                    Users[ID],
                    Users[Email],
                    USERPRINCIPALNAME()
                )
        ),
    Organization)

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;

Measure = 
ADDCOLUMNS(
    CROSSJOIN(
        FILTER(
                'Users Organizations',
                'Users Organizations'[User ID]=
                    LOOKUPVALUE(
                        Users[ID],
                        Users[Email],
                        USERPRINCIPALNAME()
                    )
            ),
        Organization),
    "Path Contains This Organization",
    PATHCONTAINS(
        Organization[Path],
        [Organization ID])
    )

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;

Measure = 
CONCATENATEX(
ADDCOLUMNS(
    CROSSJOIN(
        FILTER(
                'Users Organizations',
                'Users Organizations'[User ID]=
                    LOOKUPVALUE(
                        Users[ID],
                        Users[Email],
                        USERPRINCIPALNAME()
                    )
            ),
        Organization),
    "Path Contains This Organization",
    PATHCONTAINS(
        Organization[Path],
        [Organization ID])
    ),
    Organization[Path]&"-"&'Users Organizations'[Organization ID]&"-"&[Path Contains This Organization],
    "
    ")

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;

Measure = 
FILTER(
    ADDCOLUMNS(
        CROSSJOIN(
            FILTER(
                    'Users Organizations',
                    'Users Organizations'[User ID]=
                        LOOKUPVALUE(
                            Users[ID],
                            Users[Email],
                            USERPRINCIPALNAME()
                        )
                ),
            Organization),
        "Path Contains This Organization",
        PATHCONTAINS(
            Organization[Path],
            [Organization ID])
        ),
    [Path Contains This Organization])

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;

Measure = 
DISTINCT(
    SELECTCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                CROSSJOIN(
                    FILTER(
                            'Users Organizations',
                            'Users Organizations'[User ID]=
                                LOOKUPVALUE(
                                    Users[ID],
                                    Users[Email],
                                    USERPRINCIPALNAME()
                                )
                        ),
                    Organization),
                "Path Contains This Organization",
                PATHCONTAINS(
                    Organization[Path],
                    [Organization ID])
                ),
            [Path Contains This Organization]),
        "Organization ID",
        Organization[ID]
    )
)

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:

[ID] 
IN
DISTINCT(
    SELECTCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                CROSSJOIN(
                    FILTER(
                            'Users Organizations',
                            'Users Organizations'[User ID]=
                                LOOKUPVALUE(
                                    Users[ID],
                                    Users[Email],
                                    USERPRINCIPALNAME()
                                )
                        ),
                    Organization),
                "Path Contains This Organization",
                PATHCONTAINS(
                    Organization[Path],
                    [Organization ID])
                ),
            [Path Contains This Organization]),
        "Organization ID",
        Organization[ID]
    )
)

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:

Current User = LOOKUPVALUE(
    Users[Name],
    Users[Email],
    USERPRINCIPALNAME())

and a measure like this for all the current positions:

Current Organization = 
CONCATENATEX(
FILTER(
    Organization,
    Organization[ID] in 
    SELECTCOLUMNS(
            FILTER(
                'Users Organizations',
                'Users Organizations'[User ID]=
                    LOOKUPVALUE(
                        Users[ID],
                        Users[Email],
                        USERPRINCIPALNAME()
                    )
            ),
        "Organization ID",
        'Users Organizations'[Organization ID])
            )
,Organization[Position],", ")

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

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

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

  • 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

  • Hi Reza. Very nice article. I have a question. Let us say at one specific organization level, like region managers. These managers want to see how they are doing against other regions. Perhaps they can see summarized data per region but not any detailed data as a specific region manager can see. I guess I have to create this DRLS logic into a measure, since I cannot add extra rows into users organizations table, because then they will have access to others regions. Could you give me a hint how you would solve this?

    • Very good question which can lead to another blog article in RLS series (I’ll write about it for sure)
      the way that you can do it: is to create an overall table with one row: the average and totals only. this table won’t be related to other tables. and it means that it won’t be filtered through RLS. everyone with any roles will have access to it. but they won’t see the details of other regions, because the data in the table is aggregated.
      Cheers
      Reza

  • Excellent article and very helpful! I’ve been trying to get this to work with a “simpler” implementation, but I’m struggling a bit. I’m trying to define a “user access” table with emails and organizations that will determine what the user has access to. My other table has a corresponding “organization” column. The user access table can have multiple rows per user, so user test1@test.com has access to organizations AA and AB, for example. That would then allow RLS to filter the table for organizations AA and AB for that user.

    I’d like to avoid, if possible, to use path or pathcontains as it would incurr on too much re-work of an existing data set. Any help you could provide, would be greatly appreciated!

    • Hi Miguel.
      Two things:
      1. there is no problem of using PATH functions, I use them every week, and they work perfectly well.
      2. To give you better advice on your scenario, I really need to see each table and the sample data in each table. if you can put screenshots here, then it would be great
      Cheers
      Reza

  • Hi Reza,
    Great article and very useful.
    I have been trying to apply this into our organisation scenario, and we have something like this: one user can have multiple positions and multiple users can have the same position, e.g. two accounts.
    How can I apply DRLS so that the users can only see their own records and not see other people with the same position’s?
    Any advice would be appreciated. Thank you.

    • If you want that person see their OWN record, not their Positions’s record, then I suggest flattening the table of position’s users and transactions, that way, you can see who is the user for that specific transaction, and you can filter based on that.
      for more detailed help, I need to see your data model.
      Cheers
      Reza

  • Your articles about DRLS have been very helpful, however, I’ve not been able to solve my problem. I have a two-level hierarchy (Director –> Manager) where both of them are assigned to one or more accounts. As sometimes people change projects or leave their jobs, an account may have different directors and/or managers as time goes by. I need the current director/manager to be able to see their own records as well as the records of those accounts that were the property of another director/manager before and, at the same time, allowing the old director/manager to see its old records without seeing the new ones belonging to another person.

    • Hi
      For your situation, you need to have a bit more conditions in your DAX statements. Based on what you say, people’s roles and projects change over time, so you need to have tables with their roles and Start and End effective times, and your row-level security DAX expression should consider all of these.
      Cheers
      Reza

Leave a Reply