Forum Discussion
Replicating Power Query Formula in DAX
Simple one I imagine.
I have the seen the Power Query formula which will help with my issue I have with rturning previous and next row of data.
Problem is when doing this in Power Query it slows down the refresh down to a crawl.
Previous Row formula = Name {Index -1}
Name Index Previous Row Formula
Jeff 1 0
Bob 2 Jeff
Ian 3 Bob
Just trying to replicate that in DAX
Then I can use another formula to find the next row and hopefullly go from there.
Hi villa1980,
To replicate the previous and next row functionality in DAX, you can use DAX functions like EARLIER, and FILTER to create calculated columns for both the previous and next rows.
Here’s how you can do it in DAX:
Step 1: Create a Calculated Column for the Previous Row
This column will get the name of the previous row based on the Index column.
PreviousRow = VAR CurrentIndex = YourTable[Index] RETURN IF ( CurrentIndex = 1, BLANK(), CALCULATE( MAX(YourTable[Name]), FILTER( YourTable, YourTable[Index] = CurrentIndex - 1 ) ) )
Step 2: Create a Calculated Column for the Next Row
Similarly, you can create a calculated column to get the name of the next row.
NextRow = VAR CurrentIndex = YourTable[Index] RETURN CALCULATE( MAX(YourTable[Name]), FILTER( YourTable, YourTable[Index] = CurrentIndex + 1 ) )
Example Output
With this setup, your table might look like this:
Name Index PreviousRow NextRow
Jeff 1 BLANK() Bob Bob 2 Jeff Ian Ian 3 Bob BLANK()
9 Replies
Hi villa1980
Try this:
Prev Row = IF ( 'Table'[Index] = 1, "0", CALCULATE ( MAX ( 'Table'[Name] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ) ) )DAX is more optimized at scanning a table that Power Query but this will still depend on the table size. If your applying this on a very large table, this can be very slow as well. If so, I would use a combination of Power Query and DAX with two tables. The first table with index. The second with with index + or -1 (depending on which table does the lookup) and a LOOKUPVALUE or RELATED column.
Blimey, that was quick and worked really well, thank-you.
I have now tried this with end time, however, the outcome when looking at time comes back as
0.56,0.60
rather than 13:30:00, 14:30:00
I think a format would have to be placed in the filter, but not sure where I place itThe newly created column is type number. You can just change the data type to time as adding a FORMAT formula in the calc coumn returns a text. Take note, time format cannot be 24 hours and more which means 1.5 will be returned as 12pm and not one day and 12 hours.
Hi villa1980,
To replicate the previous and next row functionality in DAX, you can use DAX functions like EARLIER, and FILTER to create calculated columns for both the previous and next rows.
Here’s how you can do it in DAX:
Step 1: Create a Calculated Column for the Previous Row
This column will get the name of the previous row based on the Index column.
PreviousRow = VAR CurrentIndex = YourTable[Index] RETURN IF ( CurrentIndex = 1, BLANK(), CALCULATE( MAX(YourTable[Name]), FILTER( YourTable, YourTable[Index] = CurrentIndex - 1 ) ) )
Step 2: Create a Calculated Column for the Next Row
Similarly, you can create a calculated column to get the name of the next row.
NextRow = VAR CurrentIndex = YourTable[Index] RETURN CALCULATE( MAX(YourTable[Name]), FILTER( YourTable, YourTable[Index] = CurrentIndex + 1 ) )
Example Output
With this setup, your table might look like this:
Name Index PreviousRow NextRow
Jeff 1 BLANK() Bob Bob 2 Jeff Ian Ian 3 Bob BLANK() Hello villa1980
You can use the following formula to create a new calculated column in the table to get the previous row value:
Previous Name = VAR _idx = NamesTbl[Index] RETURN CALCULATE ( SELECTEDVALUE ( NamesTbl[Names] ), FILTER ( ALL ( NamesTbl ), NamesTbl[Index] = _idx - 1 ) )Here is the screenshot of the same:
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTreeyou guys amaze me honestly wow....let's see if you can solve this issue then using a similar line of thinking 🙂
So this is the table I have below, what I would like is do find the time difference between the End Time and the next row start time (which us where asking for Previous row and next row DAX).
This needs to be for the Appointment Owner, BAYID and StartDate
Sorry all, was being too complicated again. Didn't need anything else, just needed the end time against the next row and have what I need.
Thank-you again.
