DAX formula for complex SQL nested table joins

[isohawon]

Hi,

I have searched far and wide for the solution to this problem and have been unable to find a solution.

I am trying to replicate a complex SQL join in DAX. Normally, I would use the SUMMARIZECOLUMNS function. Let's say for instance that this is my SQL code:

(let's assume I've created two temp tables #appt and #orders)

Select o.UnitNumber, o.OrderDateTime, o.NextOrderDateTime, o.Malignant, (select top 1 AppointmentID from #appt a where a.VisitID = o.VisitID and (a.DateTime >= o.OrderDateTime  and (a.DateTime < o.NextOrderDateTime or o.NextOrderDateTime is null)) order by DateTime) as AppointmentID

From #orders o

So it's more like a nested join instead of a clear cut LEFT JOIN... and the problem with it is the really complex date constraint that I do not know how to replicate in DAX.

So basically in Power BI right now, I have the the #appts table and the #orders table:

Appts table has the following columns:

• VisitID
• AppointmentID
• AppointmentTypeID
• AppointmentStatus
• ApptTypeGroupID
• DateTime
• ProviderID
• Date

Orders table has the following columns

• VisitID
• AccountNumber
• UnitNumber
• OrderID
• Category
• ProviderID
• OrderDateTime
• NextOrderDateTime
• Malignant

I would really appreciate if someone could help.

[v-jiascu-msft]

Hi @isohawon,

Can you share a dummy sample? The top 1 part is hard to guess without data. It could be like below. The a.VisitID = o.VisitID will be accomplished by the relationship. 

Table =
FILTER (
    NATURALLEFTOUTERJOIN ( '#orders', '#appt' ),
    '#appt'[datetime] >= '#orders'[orderdatetime]
        && (
            '#appt'[datetime] < 'orders'[nextorderdatetime]
                || ISBLANK ( 'orders'[nextorderdatetime] ) = TRUE ()
        )
)

Best Regards,

Dale

[Anonymous]

Hello community, hello v-jiascu-msft

I have a quiet similar problem, if not the same as isohawon. His question is not answered yet, so I continue this thread hoping to find a solution.

What I am trying to accomplish is to get balances for accounts. I have two tables. A dimension table for the accounts and a fact table for the transactions. I now want to get one table as result which presents all active accounts and their current balance.

I present to you: A (simplified) datamodel. I guess this makes it easier to understand.

Also, I show to you the result table I wish to get (if used with an SQL database). Filters are aplied for 20.01.2019 or 27.03.2019.

And of course the SQL statement which can produce this results.

SELECT c1.AccountID,
(SELECT TOP 1 t1.balance FROM Transactions AS t1 WHERE c1.AccountID = t1.AccountID AND t1.date <= @Var AND t1.balance IS NOT NULL ORDER BY t1.AccountID, t1.UseCount) AS Balance
FROM Card AS c1
WHERE (SELECT TOP 1 t1.balance FROM Transactions AS t1 WHERE c1.AccountID = t1.AccountID AND t1.date <= @Var AND t1.balance IS NOT NULL ORDER BY t1.AccountID, t1.UseCount) <> 0 

We work with an SSAS databse.  I have full access to this database an can create calculated tables, columns and measueres if needed. But we do not work with Power Query since we only connect directly to our datasource.

I'd appreciate any suggestions on how to solve this problem.

Regards

[isohawon]

Hi @v-jiascu-msft,

Regarding the relationship portion, that is also one of my challenges because the VisitID is not unique in either of the two tables. So when I try to create a relationship, I am unable to do so. 😞

Regarding providing some sample data, I have some concerns about that because the data that I work with contains personal health information for our patients. But I will try to explain the logic as best as I can.

Essentially what I am trying to do with the SQL query is to find the first (Top 1) appointment that occurs after first order was put in (hence the OrderDateTime) but before the following/next order was put in for that same patient (hence the NextOrderDateTime). i.e. The appointment "top 1" or "First" appointment that occurs between those two order dates. 

I hope this explanation makes sense.

Thanks.

[TomMartens]

Hey @isohawon,

this is why @v-jiascu-msft asked for dummy data. Please consider to create a PBIX file that contains sample data, that easily allows to provide you the solution you are looking for.

Upload the pbix file to onedrive or dropbox and share the link.

Otherwise, you will "offload" the burden to create sample data to people trying to help 😉

Regards,

Tom