Forum Discussion
Implementing SQL Lag and Lead functions in Power BI Direct Query
Hi Anonymous ,
You could use SQL query like below to get result directly
SELECT *, LEAD(Days to Repeat, 1,0) OVER (PARTITION BY CustomerID ORDER BY Last Interaction ACE ) AS new FROM tableOr you could try to use below query to cretae index, then use expression in my first reply to get result
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Last Interaction ACE ) AS index FROM tableBest Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
You could try to use SQL query when you connect to dicrect query , or you also could try to add a index column in your table by sql, then you could try to use expression like below
Measure = CALCULATE(SUM('Table (4)'[amount]), FILTER(ALLEXCEPT('Table (4)','Table (4)'[name]), 'Table (4)'[index]=MIN('Table (4)'[index])-1))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi ,
I am still not able to get the right information out. The problem is probably the date field being used.
Thanks
Sud
Hi Anonymous ,
You could use SQL query like below to get result directly
SELECT *, LEAD(Days to Repeat, 1,0) OVER (PARTITION BY CustomerID ORDER BY Last Interaction ACE ) AS new FROM tableOr you could try to use below query to cretae index, then use expression in my first reply to get result
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Last Interaction ACE ) AS index FROM tableBest Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If we use direct query we cannot aggregate, merge or do any operations on the table, A pop up will show to import the entire data set, can you provide any other solution in powerbi using earlier filter.
Thanks