Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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's avatar
    OwenAuger
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.