Addressing Heirarchy

[muralidharan_79]

I need to implement RLS to the following dataset. I have a table where it has UnitID, ParentUnitID, Level and its respective UserID. Organization in here is set up in a way starting from Level 1 which is in the top of the hierarchy followed by 2, 3, 4 and 5. All these can be linked through ParentUnitID. For example X unit from Level 3 will have both UnitID and ParentUnitID, where ParentUnitID of this X Unit is linked to the level 2 unit and unitID of this X unit is linked to the Level 4 units.

Now for example, if a user has access to L3 and L5 unit (which we can consider as direct) he should also able to see the respective Level 4 units as well.

So the expected table / output is to have a one single table which should have both direct and indirect units, so that RLS can be applied on this table and filter the model. Kindly shed some ideas on this. Thanks in advance!

Sample of Units Table

[Anonymous · Accepted solution]

Hi @muralidharan_79 ,

As I understand it, if you want to rely on parentUnitID for hierarchical structure hierarchies, you can use the PATH function in DAX.

Path=PATH('Table'[UnitID],'Table'[ParentUnitID])

Use these two MEASURES to determine the ID to determine their hierarchy.

UserL3Acess=LOOKUPVALUE('Table (2)'[AccessibleUnitID],'Table (2)'[UserID],"UserA",'Table (2)'[AccessibleLevel],"L3")

UserL5Access=LOOKUPVALUE('Table (2)'[AccessibleUnitID],'Table (2)'[UserID],"UserA",'Table (2)'[AccessibleLevel],"L5")

HasAccessToL 113=IF(
    PATHCONTAINS('Table'[Path],[UserL5Access])||PATHCONTAINS('Table'[Path],[UserL3Acess]),
    1,
    0
)

By calculating the columns we can see that if level 3 and level 5 can be seen, then level 4 can also be seen.

Finally, perform the Rls setup and verify that it was successful.

If you still have other questions, you can check the PBIX file I uploaded, maybe it can help you with your question, about the use of PATH function, there is a case to talk about very detailed and easy to understand, I paste it below for you, I hope it can answer your questions.

Solved: How to Create Organization Chart in Power BI Deskt... - Microsoft Fabric Community

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.