Replicating Power Query Formula in DAX

[villa1980]

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.

[villa1980]

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.

[villa1980]

you 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

[FreemanZ]

hi @villa1980 ,

try like:

column = 

VAR _result =

MAXX(

    FILTER(

        data, 

        data[index]=EARLIER(data[index]) - 1

    ),

    data[Name]

 )

RETURN 

IF(_result=BLANK(), 0, _result)

[quantumudit]

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: LinkTree

[anmolmalviya05 · Accepted solution]

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()

[danextian]

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.

[villa1980]

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 it

[danextian]

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

[danextian]

If this solves, please mark this as the solution.