Find the current ID of this ID through a table that contains relationship.

[Anonymous]

Here in the green zone, I have 2 tables.

The table 1 is a list of IDs. Table 2 is the relation between IDs. For example. the ID 1 is replaced by ID 2, ID 2 is replaced by ID 3. The ID 3 is replaced by ID 4.

I want to make a calculated column that displays the newest replaced ID for each ID. Just like the table in the yellow zone.

 

How can I do this? I think of recursive, but it seems that DAX doesn't support this.

[Anonymous]

@AlB It didn't return the result I want, but I really appriciate your help. Thank you.

@Zubair_Muhammad : Thank you. Your solution is perfect, exactly what I want. I have test a lot of cases and it passed all of them.

But can you explain the PATHITEM syntax a bit? As far as I understand, PATHITEM returns a string that contains all the related higher ID of the current ID (In this example, it should be "4,3,2,1" for the first row) and the parameter with value =1 means the first character of that string (4). Am I understand it correctly?

[Zubair_Muhammad]

@Anonymous 

Actually the final formula can be broken down into 2 parts

1st part:    PATH ( Table1[ID], Table1[Parent] )

PATH function defines the PATH from grandest Parent to Grandest Son.
Result is like :  Category|Subcategory|SubSubCategory|Items

You can create a separate column to test above formula

2nd Part = PATHITEM(PATH,Position)

Whenever a textstring is delimited by character "|", Path item function becomes usable

So in the example above
PATHITEM1 is Category
PATHITEM2 is Sub Category

and so on

[AlB]

Hi @Anonymous 

How do you determine which one is the "newest replaced ID"?? There are no dates or similar in Table2 and bear in mind that row order does not have any meaning in DAX. I'm not sure I understand correctly, but try this to add a calculated column to Table1, where it's assumed that the "newest replaced ID" is the highest one on table 2: 

EDITED: 

Current =
CALCULATE (
    DISTINCT ( Table2[Replaced_By] ),
    FILTER ( Table2, Table2[ID] = MAX ( Table2[ID] ) )
)

[Anonymous]

Here is some clarification, for god sake, please Microsoft, dont' delete my reply again, as now I'm in a hurry.

____________

Table 1 stores invoice records, with many other attributes. But I don't list them there as I think they are not important in this case.

When ever my merchant sends me an invoice, a record in table 1 will be made. For example, Invoice with an ID = 1, 2, 3, 4, 5 ,6, 7, 8......

Sometime, the merchant reliazes that the previous invoice was wrong, so they send me a new invoice to replace the previous one. A record will be written in the table 2, example, invoice with ID = 1 is replaced by invoice with ID = 2.

After that, because the second invoice still has errors, so they send me a new one again. A new record is written to the table 2, saying that the invoice with an ID = 2 is now replaced by an invoice with an ID of 3.

After 2 days, they realizes that there are still errors in the invoice #3, they sent me a new one again. This time, invoice 4 replaces invoice 3.

SurrogateKey is just the identity.

Here invoice 1 is replaced by invoice 2, invoice 2 is replaced by invoice 3, invoice 3 is replaced by invoice 4.
Invoice 7 is replaced by invoice 8.
Invoice 4 and invoice 5 are just a new invoice. They don't replace anyone. So no records related to invoice 4, 5 exist in table 2.

I want a calculated columns that shows the newest invoice ID of each invoice. For eg, current of invoice 1, 2, 3 should be 4, current of invoice 7 should be 8. If they are the newest one, or they aren't replaced by any other invoices, the current column should be equal to their ID.

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous 

Try these calculated columns in Table1.

It works with your sample data.

See file attached as well

Parent =
VAR myvalue =
    LOOKUPVALUE ( Table2[Replaced_By], Table2[ID_ToBeReplace], [ID] )
RETURN
    IF ( ISBLANK ( myvalue ), [ID], myvalue )

FinalResult =
PATHITEM ( PATH ( Table1[ID], Table1[Parent] ), 1, INTEGER )

[Anonymous]

Sorry, but your DAX doesn't work. It displays some kind of error.

I will post some clarification in the later posts. My post has been mark as spam (!?), don't know why. I'm trying to repost it again.

[BrandyG_iTalent]

@Anonymous - 

The platform scans for spam automatically. There are multiple variables the platform scans for and we have no way of knowing exactly what triggered your post to be flagged. Some common trigger items are copy and pasting content in and multiple edits.