Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Need help on DAX - Leg Number

Hello All

 

Need your help.

 

From the below data I need to get the leg number of each invoice ID

 

For each Invoice ID first SegmentID should be leg 1 and therefore followed second segment ID should be Leg 2 and thereafter 3, 4, 5.....

 

InvoiceDetailIDInvoiceIDSegmentID
23220238706782476640
23222098707472476794
23222158707472476797
23223368707472476897
23223378707472476898
23223388707472476899
23223398707472476900
23222248707502476803
23222268707502476804
23223588707822476912
23223588707822476913
23223588707822476914
23223588707822476915
22183578355702374865
22183578355702374866
22183648355722374869
22183648355722374870
22300888396292386244
23220138706732476633
23220178706752476636

 

 

Result to be shown as below

 

InvoiceDetailIDInvoiceIDSegmentIDLegNumber
232202387067824766401
232220987074724767941
232221587074724767972
232233687074724768973
232233787074724768984
232233887074724768995
232233987074724769006
232222487075024768031
232222687075024768042
232235887078224769121
232235887078224769132
232235887078224769143
232235887078224769154
221835783557023748651
221835783557023748662
221836483557223748691
221836483557223748702
223008883962923862441
232201387067324766331
232201787067524766361
Need Help
Show and Tell
  • selimovd's avatar
    selimovd
    5 years ago

    Hey gauravnarchal ,

     

    check the following calculated column, that should make it:

    LegNumber = 
VAR vInvoiveID = myTable[InvoiceID]
VAR vSegmentID = myTable[SegmentID]
RETURN
CALCULATE(
    COUNTROWS(myTable),
    myTable[InvoiceID] = vInvoiveID
    && myTable[SegmentID] <= vSegmentID,
    ALL(myTable)
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
  • parry2k's avatar
    parry2k
    5 years ago

    gauravnarchal maybe add a rank column:

     

    Rank Column = 
RANKX ( FILTER( 'Table',  'Table'[InvoiceID] = EARLIER ( 'Table'[InvoiceID] ) ), 'Table'[SegmentID], , ASC )

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

     

2 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey gauravnarchal ,

     

    check the following calculated column, that should make it:

    LegNumber = 
VAR vInvoiveID = myTable[InvoiceID]
VAR vSegmentID = myTable[SegmentID]
RETURN
CALCULATE(
    COUNTROWS(myTable),
    myTable[InvoiceID] = vInvoiveID
    && myTable[SegmentID] <= vSegmentID,
    ALL(myTable)
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    gauravnarchal maybe add a rank column:

     

    Rank Column = 
RANKX ( FILTER( 'Table',  'Table'[InvoiceID] = EARLIER ( 'Table'[InvoiceID] ) ), 'Table'[SegmentID], , ASC )

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡