Reputation: 74
I have the next table in Power BI:
Id | Old Id |
---|---|
1 | |
2 | 1 |
3 | 2 |
4 | |
5 | |
6 | |
7 | 6 |
8 | 7 |
The column Id is the identifier of a product that last 1 month, if the product is updated then a new Id is created after a month, Old Id column shows the previous Id that the product had, where the product comes from.
What I am trying to get is that based on the sequence of Old Id, try to identify the last Id that belongs to the original ID as is showed in this table:
Id | Old Id | Result |
---|---|---|
1 | 3 | |
2 | 1 | |
3 | 2 | |
4 | ||
5 | ||
6 | 8 | |
7 | 6 | |
8 | 7 |
I am working in DAX, so I have read that looping is not an option.
Upvotes: 0
Views: 1858
Reputation: 3741
You can try this approach:
Add new column to your table as:
PathIt = PATHITEM(PATH(Sheet2[Id],Sheet2[Old Id]), 1)
Next create a new measure:
LastID = CALCULATE( max(Sheet2[Id]), ALL(Sheet2), TREATAS( { SELECTEDVALUE(Sheet2[Id]) }, Sheet2[PathIt] ))
Upvotes: 2