Joining tables with multiple conditions and functions

[Anonymous]

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

[TeigeGao]

Hi @Anonymous ,

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

[Anonymous]

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