ferolo3000
ferolo3000

Reputation: 74

How to loop in a table using DAX / Power BI

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

Answers (1)

msta42a
msta42a

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] ))

enter image description here

Upvotes: 2

Related Questions