cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
StormGroup Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: Constructing a historical hierarchy based on a changing parent.

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Constructing a historical hierarchy based on a changing parent.

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

StormGroup Frequent Visitor
Frequent Visitor

Re: Constructing a historical hierarchy based on a changing parent.

Hi @v-shex-msft 

 

Thanks anyway 🙂

 

/ Martin

AndrewMachiano Frequent Visitor
Frequent Visitor

Re: Constructing a historical hierarchy based on a changing parent.

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 168 members 1,759 guests
Please welcome our newest community members: