Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Get next 3 row values for current row

I already got the solution for the next table with Power Query (grouping, indexing, combining, expanding, etc.), but when I load all the data, Power BI can't process it, it takes hours without getting the final report.
I have the "Sequence" (it is already grouped) and "Value" columns and at the end I added three indexes and combined the table also three times against itself (more than half millon rows aprox), to get the next three values; I think these three final combining and expanding processes are the reason for this delay. Somebody can supply a DAX code to avoid long process time.
By the way, the previous combining and expanding commands are also slow, but at least take no more than 5 minutes to process.

 

Source Resuts  
SequenceValueNext1Next2Next3
1SKUMTTOCIPCAM
2MTTOCIPCAMSKU
4CIPCAMSKUCAM
5CAMSKUCAMMTTO
6SKUCAMMTTO 
10CAMMTTO  
11MTTO   
Need Help
Tips and Tricks
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    If you want a measure in table visual, parry2k’s solution is proper and great.

    But if you want actual column in table, We can create following calculate columns to meet your requirement.

     

    Next 1 = 
var x = CALCULATE(MIN('Table'[Sequence]),FILTER('Table','Table'[Sequence]>EARLIER('Table'[Sequence])))
return
CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Sequence]=x))

     

    Next 2 = 
var x = CALCULATE(MIN('Table'[Sequence]),FILTER('Table','Table'[Sequence]>EARLIER('Table'[Sequence])))
return
CALCULATE(MAX('Table'[Next 1]),FILTER('Table','Table'[Sequence]=x))

     

    Next 3 = 
var x = CALCULATE(MIN('Table'[Sequence]),FILTER('Table','Table'[Sequence]>EARLIER('Table'[Sequence])))
return
CALCULATE(MAX('Table'[Next 2]),FILTER('Table','Table'[Sequence]=x))

     

    And the result like this,

     

     

    BTW, pbix as attached.

     

    Best regards,


    Community Support Team _ zhenbw
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    OK, I'm missing how you get from Source to Results, can you explain that? I am assuming Source is your source data and includes Sequence and Value columns and that Results is what you want to end up with or am I mistaken?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg,

      That's right. Columns "Sequence" and "Value" are the source data, and results are what I need.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Comment: The numbers in the "Sequence" column are illustrative, because they are the result of previous grouping commands. I use that column only to preserve date/time stamp, removed from this last table. I removed all unnecessary columns to avoid excessive processing time.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    If you want a measure in table visual, parry2k’s solution is proper and great.

    But if you want actual column in table, We can create following calculate columns to meet your requirement.

     

    Next 1 = 
var x = CALCULATE(MIN('Table'[Sequence]),FILTER('Table','Table'[Sequence]>EARLIER('Table'[Sequence])))
return
CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Sequence]=x))

     

    Next 2 = 
var x = CALCULATE(MIN('Table'[Sequence]),FILTER('Table','Table'[Sequence]>EARLIER('Table'[Sequence])))
return
CALCULATE(MAX('Table'[Next 1]),FILTER('Table','Table'[Sequence]=x))

     

    Next 3 = 
var x = CALCULATE(MIN('Table'[Sequence]),FILTER('Table','Table'[Sequence]>EARLIER('Table'[Sequence])))
return
CALCULATE(MAX('Table'[Next 2]),FILTER('Table','Table'[Sequence]=x))

     

    And the result like this,

     

     

    BTW, pbix as attached.

     

    Best regards,


    Community Support Team _ zhenbw
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you v-zhenbw-msft ! It's the solution, I was looking for.