Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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__
Solved! Go to 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.
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 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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |