Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
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.
User | Count |
---|---|
48 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
57 | |
29 | |
20 | |
16 |