Viewing the data as a hierarchy for data which has an internal hierarchy

[afaro]

I have data like this 

Key	Transaction ID	Parent Transaction ID
K1	3	2
K1	2	1
K1	1	0
K2	3	2
K2	2	1
K2	1	0

I want to identify the level 1 parent for all of these but as the transaction ID itself is not unique but it is unique per Key then how do I go about doing this? 

I want to use the PATH and PATHITEM variables for this? Also, how do I create a hierachy if the number of levels is unknown to me? 

[v-jialongy-msft]

Hi @afaro

Step 1: Create a Unique Identifier

Given your description, you might concatenate the Key and Transaction ID,Parent Transaction ID to form a unique identifier for each transaction. For example, you can create a new column in Power Query or using DAX:

UniqueID = [Key] & [Transaction ID]
ParentUniqueID = [Key] & [Parent Transaction ID]

Step 2: Use PATH to Create Hierarchy

Once you have unique identifiers, use the PATH function to create a hierarchy path. The PATH function requires two parameters: the unique identifier of the current row and the unique identifier of the parent row.

HierarchyPath = PATH([UniqueID], [ParentUniqueID])

Step 3: Identify Level 1 Parent

To identify the level 1 parent, use the PATHITEM function. This function extracts an item from the path created by PATH, based on its position. Since you're interested in the level 1 parent, you would use:

Level1Parent = PATHITEM([HierarchyPath], 1) 

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.