Create a new sort column based in two columns relationship (sequence)

[Anonymous]

In Power BI, I have a table called ESTRUTURAS that has two columns: one called 'Equipamento' (name of the equipment) and the other 'EquipamentoVante' which determines the name of the next equipment in the order. I want to create a column that numbers the order of these equipment. The equipment that does not have next equipment is the last equipment in the set. And the equipment that does not appear in the next equipment column is the first equipment in the set. How do I create this new column (numbering the equipment in a given set of 1, 2, 3, ... until the last equipment) in DAX language?

#Ultima = ISBLANK('ESTRUTURAS'[EquipamentoVante])      *** column that marks (true) the last equipment in order

#First = IF(

NOT(

(ESTRUTURAS[Equipamento]) IN VALUES(ESTRUTURAS[EquipamentoVante])

),

1,

0

)                   ***  column that marks the first equipment (identified as 1)

*** All my code attempts result in circular reference or other error.

[OwenAuger · Accepted solution]

Hi @Anonymous 

I believe we can do something with PATH functions. Exact code would depend on the structure of the data.

Could you post a sample of the ESTRUTURAS table? 

Questions:

• Is each value in Equipamento unique?
• If not unique, then is there a grouping column (like Order ID or something else) which groups Equipamento into sets of unique values?

Regards

[Anonymous · Accepted solution]

Great solution.
I had to create a column inverting the relationship (a column that informs the previous equipment, instead of the one that existed informing the next equipment).
Then just apply the PATH and PATLENGTH functions.

Problem solved.
Thank you very much.

[OwenAuger · Accepted solution]

Hi @Anonymous 

I believe we can do something with PATH functions. Exact code would depend on the structure of the data.

Could you post a sample of the ESTRUTURAS table? 

Questions:

• Is each value in Equipamento unique?
• If not unique, then is there a grouping column (like Order ID or something else) which groups Equipamento into sets of unique values?

Regards