Forum Discussion

isohawon's avatar
isohawon
Frequent Visitor
7 years ago

DAX formula for complex SQL nested table joins

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 SUMMAR...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

Hello community, hello 

 

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