Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to create a Table to be used as RLS. The idea is to use my existing Parent and Id columns from Table A.
Table A
UserParent | emailParent | userIdParent | Sub | IdSub |
User1 | email1@mail.mail | 1 | User1 | 1 |
User1 | email1@mail.mail | 1 | User10 | 10 |
User1 | email1@mail.mail | 1 | User11 | 11 |
User1 | email1@mail.mail | 1 | User12 | 12 |
User1 | email1@mail.mail | 1 | User13 | 13 |
User1 | email1@mail.mail | 1 | User14 | 14 |
User1 | email1@mail.mail | 1 | User15 | 15 |
User2 | email2@mail.mail | 2 | User1 | 1 |
User2 | email2@mail.mail | 2 | User2 | 2 |
User3 | email3@mail.mail | 3 | User1 | 1 |
User3 | email3@mail.mail | 3 | User3 | 3 |
User4 | email4@mail.mail | 4 | User1 | 1 |
User4 | email4@mail.mail | 4 | User4 | 4 |
What I expect is getting from Parent, all child from all levels, not only from level below, but all levels below (my table have a lot of levels, its not fixed, because levels can be added or removed).
Level 1 -> Get all Child users (Level 2,3,4,5,6..)
Level 2 -> All users from Level below (Level 3,4,5,6..)
Level 3 -> All users from Level below (Level 4,5,6..)
(....)
Like this table:
All of the "Sub" column that are in bold are the ones I want to get in the new table:
UserParent | emailParent | userIdParent | Sub | IdSub |
User1 | email1@mail.mail | 1 | User1 | 1 |
User1 | email1@mail.mail | 1 | User10 | 10 |
User1 | email1@mail.mail | 1 | User11 | 11 |
User1 | email1@mail.mail | 1 | User12 | 12 |
User1 | email1@mail.mail | 1 | User13 | 13 |
User1 | email1@mail.mail | 1 | User14 | 14 |
User1 | email1@mail.mail | 1 | User15 | 15 |
User2 | email2@mail.mail | 2 | User1 | 1 |
User2 | email2@mail.mail | 2 | User2 | 2 |
User2 | email2@mail.mail | 2 | User10 | 10 |
User2 | email2@mail.mail | 2 | User11 | 11 |
User2 | email2@mail.mail | 2 | User12 | 12 |
User2 | email2@mail.mail | 2 | User13 | 13 |
User2 | email2@mail.mail | 2 | User14 | 14 |
User2 | email2@mail.mail | 2 | User15 | 15 |
User3 | email3@mail.mail | 3 | User1 | 1 |
User3 | email3@mail.mail | 3 | User3 | 3 |
User3 | email3@mail.mail | 3 | User2 | 2 |
User3 | email3@mail.mail | 3 | User10 | 10 |
User3 | email3@mail.mail | 3 | User11 | 11 |
User3 | email3@mail.mail | 3 | User12 | 12 |
User3 | email3@mail.mail | 3 | User13 | 13 |
User3 | email3@mail.mail | 3 | User14 | 14 |
User3 | email3@mail.mail | 3 | User15 | 15 |
User4 | email4@mail.mail | 4 | User1 | 1 |
User4 | email4@mail.mail | 4 | User4 | 4 |
User4 | email4@mail.mail | 4 | User2 | 2 |
User4 | email4@mail.mail | 4 | User10 | 10 |
User4 | email4@mail.mail | 4 | User11 | 11 |
User4 | email4@mail.mail | 4 | User12 | 12 |
User4 | email4@mail.mail | 4 | User13 | 13 |
User4 | email4@mail.mail | 4 | User14 | 14 |
User4 | email4@mail.mail | 4 | User15 | 15 |
Solved! Go to Solution.
@fcarvalho , Have you already explored this blog?
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
I think I achieved what I wanted, I'll do some tests and then close the post
I think I achieved what I wanted, I'll do some tests and then close the post
@fcarvalho , Have you already explored this blog?
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
Hi @amitchandak ,
It is a very good article, but it does not fit my case, because in my data set, a child level sometimes has 2 different parentId or more and I get the error that some value has multiple values.
Another situation is to have to add 1 column per level manually. If there is an alternative, I would appreciate it.
Regards,
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |