Forum Discussion

unkCandyd's avatar
unkCandyd
Frequent Visitor
3 years ago
Solved

Converting SQL to DAX function lag by partition

Hello Community, 

 

I would like to know if possible to convert this sql query to dax query. 

 

here is the query: 

SELECT * ,
Lag(User, 1,'') OVER( PARTITION BY id
ORDER BY Date ASC) AS UserTarget
FROM table

 

my table is: 

iddateactivitytarget actusertargetuser
110-Octeatdrinkab
110-Novdrinksleepbc
112-Decsleepxxxxcxxxx
210-Octeatdinkab
210-Novdrinksleepbc
212-Decsleepxxxxxcxxxxx

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago
    UserTarget = 
MAXX(
    TOPN(
        1,
        FILTER(
            TBL,
            TBL[id] = EARLIER( TBL[id] )
                && TBL[date] > EARLIER( TBL[date] )
        ),
        TBL[date], ASC
    ),
    TBL[user]
)

1 Reply

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    3 years ago
    UserTarget = 
MAXX(
    TOPN(
        1,
        FILTER(
            TBL,
            TBL[id] = EARLIER( TBL[id] )
                && TBL[date] > EARLIER( TBL[date] )
        ),
        TBL[date], ASC
    ),
    TBL[user]
)