Manage Multi-Hierarchy Roles

[pablorturco]

Hey all !

I am trying to set a different view of my report depends on the user who opens the report. 

I have 3 differents levels of categories. (Like in the picture)

                          (SubCategory Table)

And I have a 'Permission Table' that contains the acces level to all of the users who are going to read the report. It looks like this

And then, there is a Transaction Table, where there is all the information about transacions related to a SubCategory and a few more columns with more details like 'Date', 'Sales' and 'TransacionName'. 

I need to create a Role Hierarchy that the Transacion table is filtered depending on the user( email) and the level of access. For example.

•        username3 can acces only to data related to SubCategory = id06
•        username2 can see all the subcategories whithin x3 PreCategory
•        username1 will see all the PreCategories and Subcategories within Category = RD

I have tried many solutions. I started creating different relations between the permission table and the category hierarchy (all 3 tables) but PowerBI doesnt allow you to create several bidirectionals relations starting in the same table. 

Also i have try to specify this relations in the DAX expression belonging to the Role. So I Set a Role called RLS and looks like this.

ROLE RLS

TABLE	FILTER	DAX EXPRESSION
CATEGORY	YES	[Category] =      CALCULATE( MAX('Master'[Filter]),   FILTER(Master, '    Master'[Level] = 3),  USERELATIONSHIP('Permissions'[user],'Master'[login]) )
PRECATEGORY	YES	[PreCategory] = CALCULATE( MAX('Master'[Filter]), FILTER(Master, 'Master'[Level] = 2), USERELATIONSHIP('Permissions'[user],'Master'[login]) )
SUBCATEGORY	YES	[SubCategory] =      CALCULATE( MAX('Master'[Filter]),   FILTER(Master, '    Master'[Level] = 1),  USERELATIONSHIP('Permissions'[user],'Master'[login]) )
PERMISSIONS	YES	[username] = USERPRINCIPALNAME()
TRANSACTIONS	NO

Table MASTER  is a summary of the PERMSSIONS TABLE that helps me to distinguish which filter is used depending on the user.

Table master looks like this:

Login	Level	Filter
username1@random.com	3	RD
username3@random.com	1	id06
username3@random.com	2	x3
username3@random.com	3	RD
username2@random.com	2	x3
username2@random.com	3	RD
username4@random.com	3	YL
username4@random.com	2	x7
username5@random.com	3	YL
username6@random.com	1	id13
username6@random.com	2	x8
username6@random.com	3	YL

This solution isnt working properly but i cant see any other better options. 

Hope you understand this problem and could help me with any tips.

Thank you in advance ! 

[lbendlin]

Pleae check this tutorial

Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship - RADACAD