Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EvilGranny
Frequent Visitor

Joining tables with multiple conditions and functions

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

2 REPLIES 2
TeigeGao
Solution Sage
Solution Sage

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.