Forum Discussion
Implementing SQL Lag and Lead functions in Power BI Direct Query
| CustomerID | Last Interaction | Days to Repeat |
| X | 18/07/2019 | NULL |
| Y | 02/01/2019 | -29 |
| Y | 31/01/2019 | -84 |
| Y | 25/04/2019 | 0 |
| Y | 25/04/2019 | -156 |
| Y | 28/09/2019 | -23 |
| Y | 21/10/2019 | NULL |
| Z | 05/09/2019 | -56 |
| Z | 31/10/2019 | NULL |
Hi Everyone,
I am stuck with a problem where I need to see when a customer repeat contacts us using dates within the same column. I am using a SQL lead function to get the number of days the customer takes to contact me again (see table attached). I want to do this in Power BI Direct query import but it is not allowing me to do so. I want to use direct query only as the data I have is over 600,000 rows and I want to eliminate taking excel outputs from SQL as the process gets cumbersome.
Any leads on this would be really appreciated 🙂
Regards
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.
4 Replies
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 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 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.