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.
Hello comrades,
I'm completely new with PowerBI, but not really bad with SQL.
But now I need help with the advice how to reproduce pretty strange SQL request by using Power Query or/and DAX...
I have a Requests table with columns: User_ID, Request_ID and ReceivedDate.
And I need to reproduce next sql request:
select
r1.User_ID,
r1.Request_ID,
r1.ReceivedDate,
case
when r3.MaxReceivedDate is null then '2100-01-01 00:00:00.000'
else r3.MaxReceivedDate
end as 'MaxReceivedDate'
from Requests r1
LEFT JOIN
(
select r.Request_ID, r.ReceivedDate, DATEADD(s,-1,MIN(r2.ReceivedDate)) as 'MaxReceivedDate'
from Requests r, Requests r2
where r.User_Id = r2.User_Id
and r.ReceivedDate < r2.ReceivedDate
group by r.Request_ID, r.ReceivedDate, r2.User_Id
) r3
ON r1.Request_ID= r3.Request_ID
Hi @EvilGranny ,
We can do that with DAX query.
First we can handle the table r3, we can use calculatetable to filter the data r.User_Id = r2.User_Id and r.ReceivedDate < r2.ReceivedDate, then we can use summarize to group data by r.Request_ID, r.ReceivedDate, r2.User_Id, then we can use addcolumns to get the table r3.
Then operate left joining, we can use the NATURALLEFTOUTERJOIN function.
But based on my experience, we'd better handling these steps in RDBMS to get better performance.
Best Regards,
Teige
Sorry, but I'm not sure that I've completely understand how to reproduce the most important part of the query in DAX:
(select r.Request_ID, r.ReceivedDate, DATEADD(s,-1,MIN(r2.ReceivedDate)) as 'MaxReceivedDate'
from Requests r, Requests r2
where r.User_Id = r2.User_Id
and r.ReceivedDate < r2.ReceivedDate
group by r.Request_ID, r.ReceivedDate, r2.User_Id
) r3
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |