Get all Child record from all levels

[fcarvalho]

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

[amitchandak · Accepted solution]

@fcarvalho , Have you already explored this blog?

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

[fcarvalho · Accepted solution]

@amitchandak ,

https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po...

I think I achieved what I wanted, I'll do some tests and then close the post

[amitchandak · Accepted solution]

@fcarvalho , Have you already explored this blog?

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

[fcarvalho]

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