Get next 3 row values for current row

[Anonymous]

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
Sequence	Value	Next1	Next2	Next3
1	SKU	MTTO	CIP	CAM
2	MTTO	CIP	CAM	SKU
4	CIP	CAM	SKU	CAM
5	CAM	SKU	CAM	MTTO
6	SKU	CAM	MTTO
10	CAM	MTTO
11	MTTO

[v-zhenbw-msft · Accepted solution]

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]

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

[Greg_Deckler]

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]

Hi Greg,

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

[Anonymous]

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.

[parry2k]

@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]

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]

@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]

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.