Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |