Implementing SQL Lag and Lead functions in Power BI Direct Query

[Anonymous]

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__

[dax]

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.

[Anonymous]

Hi ,

I am still not able to get the right information out. The problem is probably the date field being used. 

Thanks

Sud

[dax · Accepted solution]

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 table
 

Or 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 table
 

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.

[Anonymous]

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