Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 N...
Need Help
  • dax's avatar
    dax
    6 years ago

    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.

dax's avatar
dax
Community Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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