Retreive Hierarchy of Organization tree

[kmadimar]

Spoiler

I have two tables :

COL_URE_BU  (fact table )contains all the roles of all users and their perimeter of visibility

For exemple id user 1 has a role of administrator of a group he/she'll have unique ure (URE_ID) and the ID of organization units and entities to which a user has visibility. But this tables do not contain hierarchy details, it only contains the id of the perimeter of his visibility ( all levels) 

COL_URE_BU	Association table between users unique URE_ID and Organization/Entity ID
URE_ID	Unique id that is created based on the user role

OBJ_ORGA_CONSO_FULL_10( dim)  contains the columns with id of each hierarchy level and include all the possible combinations of the organization hierarchy that contains in dataset 

OBJ_ORGA_CONSO_FULL_10	Table that contains organization hierarchy
ORGANIZATIONUNIT_ID	Last entity ID
ORGA_1 to ORGA10	Organization id from level 1 to level 10

I want to retreive parent/child of user visibility from OBJ_ORGA_CONSO_FULL_10( which contain reference of all entites)  and organization to my COL_URE_BU

 

[levangelista · Accepted solution]

Hi Kamilya,

I think this link can be helpful for your case :

https://p3adaptive.com/2019/02/powerquerymagic-conditional-joins-using-table-selectrows/

Kind regards,

L. Evangelista