How to set up a dynamic RLS based on user profile and hierarchy in Power BI?

                                                                                                                        Check list of all posts

Context

There are two typical solutions for dynamic RLS: 1) based on user profile, which indicates what user can access what item(s); 2) based on hierarchy, such as organizational hierarchy. However, this document will introduce a practical requirement that doesn't follow the solutions mentioned above. So instead, this new solution is intended to resolve a problem based on both profile and hierarchy.

 

Requirement

For the sake of explanation, we use a simple sample.  

1)     We have a hierarchy as below

Level1	Level2	Level3
Item11	Item21	Item31
		Item32
	Item22	Item33
		Item34

 

2)     Each item has a fact below

Item	Measure
Item11	100
Item21	50
Item22	50
Item31	10
Item32	20
Item33	30
Item34	40

 

3) We have four users assigned to access an item

User 1
Level1	Level2	Level3
Item11	Item21	Item31
		Item32
	Item22	Item33
		Item34
User 2
Level1	Level2	Level3
Item11	Item21	Item31
		Item32
	Item22	Item33
		Item34
User 3
Level1	Level2	Level3
Item11	Item21	Item31
		Item32
	Item22	Item33
		Item34
User 4
Level1	Level2	Level3
Item11	Item21	Item31
		Item32
	Item22	Item33
		Item34

 

Now, we need to Model Power BI RLS to show the data below as step 1

User 1
Level1	Level2	Level3
Item11 100 300	Item21	Item31
		Item32
	Item22	Item33
		Item34
User 2
Level1	Level2	Level3
Item11	Item21 50  80	Item31
		Item32
	Item22	Item33
		Item34
User 3
Level1	Level2	Level3
Item11	Item21 50 80	Item31  10
		Item32
	Item22	Item33
		Item34
User 4
Level1	Level2	Level3
Item11	Item21	Item31
		Item32  20
	Item22	Item33
		Item34

 

Please note that the number with green colour is correct only.

 

In addition, we want to hide whatever items that are not allowed to show.

User 1
Level1	Level2	Level3
Item11 100 300	--	--
		--
	--	--
		--
User 2
Level1	Level2	Level3
--	Item21 50  80	--
		--
	--	--
		--
User 3
Level1	Level2	Level3
--	Item21 50 80	Item31  10
		--
	--	--
		--
User 4
Level1	Level2	Level3
--	--	--
		Item32  20
	--	--
		--

 

Analysis

 

There are a few challenges:

1) It is not a simple profile-based RLS, as we can't directly connect an item to the fact table. Instead, it is via hierarchy. We should see the result from the item that the user can access, but we also need to add all underlying items.

2) It is not a simple hierarchical RLS, as each user can only access more than one item. So the traditional solution with DAX path can't work either.

3) Assuming that the hierarchy is big and deep, pure DAX may not work due to poor performance.

 

Solutions

To resolve these challenges above, we have introduced the extended hierarchy with parents and all their listed children. It is derived from the hierarchy with reclusive functions such as CTE with SQL server.

ItemParent	ItemChild
Item11	Item11
Item11	Item21
Item11	Item22
Item11	Item31
Item11	Item32
Item11	Item33
Item11	Item34
Item21	Item21
Item21	Item31
Item21	Item32
Item22	Item22
Item22	Item33
Item22	Item34
Item31	Item31
Item32	Item32
Item33	Item33
Item34	Item34

 

This table will provide a bridge between the user profile and a hierarchy.

 

Implementation

1)     Create all tables listed below

2)     Model all these tables; please note the relationship between UserItem and ItemEx is many to many bi-directional relationships, the relationship between ItemEx and Item is many to one bi-directional relationship.

 

 

3)     Define RLS as simply as below

[Email] = USERPRINCIPALNAME()

 

4)     Test result

Dynamic data mask for column, or hide items is impossible for Power BI

 

1)     In order to hide column, we need to use DAX to check whether this item is in UserItem table, if yes, then show, otherwise then hidden.

 

Show =

VAR vShow =

    CALCULATE (

        INT ( NOT ISEMPTY ( 'UserItem' ) ),

        TREATAS ( VALUES ( 'Item'[Level1] ), UserItem[Item] )

    )

RETURN

    IF ( vShow, MAX ( 'Item'[Level1] ), "--" )

 

2)     However, it is always not hidden, as the security filter does NOT apply. Then we can add security directly in DAX as below

 

Show =

VAR vShow =

    CALCULATE (

        INT ( NOT ISEMPTY ( 'UserItem' ) ),

        TREATAS ( VALUES ( 'Item'[Level1] ), UserItem[Item] ),

        FILTER ( 'User', User[Email] = USERPRINCIPALNAME () )

    )

RETURN

    IF ( vShow, MAX ( 'Item'[Level1] ), "--" )

 

But it doesn’t work due to the error below

 

 

3)     The only waywe can do is to make it as a measure, however, but then we need to Power to have Row context as below, it doesn’t make big sense anymore.

 

4)     What was the real reason for Power BI?

As the error message indicates, you cannot use USERNAME or USERPRINCIPALNAME within a calculated column or a calculated table. This is for the same reason that you can't reference slicer selections in calculated columns/tables. I.e., the model is computed once when the data is loaded and not every time you change a slicer or visit with a new user. Calculated columns are pre-calculated.  This is why the security can’t apply for calculated columns at all.

 

On the other hand, Cognos has no problem handling this requirement, as Parameters-map provides functionality to dynamically get values, then send newly generated requests to either database ( CQM), or cash (DQM) to get the requested result. Please check https://yinsolutions.blogspot.com/2020/08/using-cognos-framework-manager.html for detail.