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 gettin...
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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous here you go, you can surely merge these measure to not create sequence measures

 

Next 1 Sequence = 
VAR __currentRow = SELECTEDVALUE ( Next[Sequence] )
RETURN CALCULATE ( MIN ( Next[Sequence] ), ALL ( Next) , Next[Sequence] > __currentRow )

Next 2 Sequence = 
VAR __currentRow = [Next 1 Sequence]
RETURN CALCULATE ( MIN ( Next[Sequence] ), ALL ( Next) , Next[Sequence] > __currentRow )

Next 3 Sequence = 
VAR __currentRow = [Next 2 Sequence]
RETURN CALCULATE ( MIN ( Next[Sequence] ), ALL ( Next ) , Next[Sequence] > __currentRow )

Next 1 = 
VAR __sequence = [Next 1 Sequence]  
RETURN CALCULATE ( SELECTEDVALUE ( Next[Value] ), ALL(),  Next[Sequence] = __sequence )

Next 2 Sequence = 
VAR __currentRow = [Next 1 Sequence]
RETURN 
CALCULATE ( MIN ( Next[Sequence] ), ALL ( Next) , Next[Sequence] > __currentRow ) 
* DIVIDE ( __currentRow, __currentRow )

Next 3 Sequence = 
VAR __currentRow = [Next 2 Sequence]
RETURN CALCULATE ( MIN ( Next[Sequence] ), ALL ( Next ) , Next[Sequence] > __currentRow )
 *
DIVIDE ( __currentRow, __currentRow )

Value Max = SELECTEDVALUE( Next[Value] )

 

and here is the output

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi parry2k !

I'm missing how did you calculate "Next 2" and "Next 3" measures, since you send "Next 2 Sequence" and "Next 3 Sequence" twice. 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous not sure if I understood your question, drop all sequence measures in the table visual and you will get the answer.

     

    Would appreciate Kudos 🙂 if my solution helped.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much parry2k !

      Sorry, but I'm not (still) such an expert to use table visual and I didn't require the solution as an added column.