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

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.