Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
StormGroup
Frequent Visitor

Constructing a historical hierarchy based on a changing parent.

Hi guys,

 

I have been banging my head on this for a few days now.

 

My problem: I have a classical child/parent relationship between my users, constructing user team based on this function (in my users table); PATH(users[id],users[parent_id]). However, my problem occurs when the parent_id is updated all the activity conducted by that child user is moved to the new team. I would like to keep the activity in the team, that the person belonged to when the sale was created.

 

My solution (so far): to construct the historical hierarchy I figured that I needed to store the parent_id at sale level, in other words saving parent_id against all sales (the parent that the child belongs to at submission time). So, this means that I now have both user_id and parent_id available in my sales table.

 

Where I am stuck: Because my sales table contains multiple user entries (a row is created for every sale the user makes), and the fact that the parent is not necessarily contained as a child in my sales table. I am only able to create a first level historical hierarchy, which is insufficient.

 

In a nut shell: I am trying to construct the hierarchy described above ( PATH(users[id],users[parent_id]) ), but on sale level to store the team history of the user.

 

Any ideas?

 

Thanks!

/ Martin

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @StormGroup ,

>> I would like to keep the activity in the team, that the person belonged to when the sale was created.

Nope, this is impossible. AFAIK, power bi not support to cache history records, every time it updated old records will been replaced.

BTW, parent child relationship is one parent to multiple child node(tree relationship), it not support to mapping one child node to multiple parents.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @StormGroup ,

>> I would like to keep the activity in the team, that the person belonged to when the sale was created.

Nope, this is impossible. AFAIK, power bi not support to cache history records, every time it updated old records will been replaced.

BTW, parent child relationship is one parent to multiple child node(tree relationship), it not support to mapping one child node to multiple parents.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft 

 

Thanks anyway 🙂

 

/ Martin

Anonymous
Not applicable

I know I'm a little late to the party, but I ran up against a very similar use case a couple of weeks ago and I was able to solve it.

 

I have several reports for a company that underwent a structural reorganization last year.  Many of the same people are still at the company but due to the re-org the reporting structure was flattened, meaning less management layer.  Also, some of those people were promoted and there is a fair amount of employees moving between supervisors.  This presented a significant challenge because the hierarchy was obviously very fluid.  Also, there was a requirement that any activity in certain fact table had to reflect the org hierarchy not only on any given day, but any span of time.

 

After much Googling and not finding much and also plenty of banging my own head against a wall, I realized that if I created a composite key that combined the Employee ID (and consequently, the Supervisor's ID) with the date, I could accomplish my goal provided that each row in the affected fact tables had the Employee and Supervisor ID and a date in it.  It gave me a unique ID for each employee for every day that was able to be used recursively in the normal parent-child relationship because for any given day, there was a unique Child and Parent ID set.

 

My date table had a date key consisting of the date in ISO form expressed as an integer.  In Power Query I simply merged the date key into each row based on the date column in the affected fact tables (obviously).  After loading the model, it was pretty simple to use the PATH function the same way you did and then use LOOKUPVALUE and PATHLENGTH to get the names of the supervisors up to the desired level.

 

I'm not saying this will definitely work for you because you said the supervisor doesn't necessarily appear in every row in the Sales fact table BUT if you can change that and get that brought in, this may work for you.

 

Alternatively, if you still can't get the Supervisor into each row in the Sales table, you may be able to use the Sales table or maybe another source to construct an employee history table that tracks the exact org hierarchy on any given day during the timespan you are looking at.  I did something similar in a different report (luckily they were only looking back to last October and there were max 1700 employees) and was able to construct a table that had a listing of every active employee, their job title, and their supervisor for every day from last October to this October.  I used the composite key strategy to get the hierarchy for each day and then used PATH and the other methods I listed above to get the Supervisor(s) as far up the chain as they wanted.

 

I know this is a kludge and may not be practical based on factors such as ability to get supplemental data that could help you build the employee history table, the number of employees you're looking at, and the time span you're looking at.

 

However, it may be something that you can try.  I can't post any screenshots because the data isn't public and I don't have any sample data handy.  Let me know if you have any questions, I'll try to keep an eye on this thread to see if any new responses come in.

 

Best of luck,

 

Andrew

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.