topic Re: Need help to convert this LEFT JOIN in DAX Commands and Tips

Need help to convert this LEFT JOIN

Anonymous — Wed, 11 Nov 2020 07:34:03 GMT

Hi Floks,
I very tried of trying to convert this query to DAX.
Can anybody please help to get out of this.

My Query:

select * from
(Select B.SiteID, B.PartID, A.Length, A.Width
from factInventoryTransactions A join dimpart B
on A.Part_SK = B.Part_SK

where A.TransactionType in(6,4)
) Z

left join
(Select B.PartID, A.Length, A.Width
from factInventoryTransactions A join dimpart B on A.Part_SK = B.Part_SK
) Y
on Z.PartID = Y.PartID and Z.Length = Y.Length and Z.Width = Y.Width

where A.TransactionType in(7,5)
where Y.PartID is null

Thanks in advance ,
Sivanesan C

Re: Need help to convert this LEFT JOIN

amitchandak — Wed, 11 Nov 2020 07:25:11 GMT

Anonymous , what is a formula you want to achieve using these joins, the second join with dim part needs a combined concatenated key . Also, join would be run time. so need to know why we want to join parts in both queries

Re: Need help to convert this LEFT JOIN

Anonymous — Wed, 11 Nov 2020 07:32:04 GMT

Hi amitchandak ,
First of all the above query have msitake,Below query is I want to convert .

select * from
(Select B.SiteID, B.PartID, A.Length, A.Width
from factInventoryTransactions A join dimpart B
on A.Part_SK = B.Part_SK
where A.TransactionType in (6,4)
) Z

left join

(Select B.PartID, A.Length, A.Width
from factInventoryTransactions A join dimpart B on A.Part_SK = B.Part_SK
where A.TransactionType in(7,5)
) Y
on Z.PartID = Y.PartID and Z.Length = Y.Length and Z.Width = Y.Width
where Y.PartID is null

Here i trying to pick the un closed orders, this is the logic in SQL
But i need to show it in Power BI.

Thanks,
Sivanesan C

Re: Need help to convert this LEFT JOIN

amitchandak — Wed, 11 Nov 2020 08:57:07 GMT

Anonymous ,Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data. I can replicate as DAX but measure in power bi need different approch

Re: Need help to convert this LEFT JOIN

wdx223_Daniel — Thu, 12 Nov 2020 06:32:45 GMT

Anonymous assume you already got a dimpart table and factinventor Table in the model, and then try this code

Table3 = VAR LeftTbl = SUMMARIZE ( FILTER ( FactTable, FactTable[TransactionType] IN { "4", "6" } ), dimTable[SiteID], dimTable[PartID], FactTable[Length], FactTable[Width] ) VAR RightTbl = SELECTCOLUMNS ( SUMMARIZE ( FILTER ( FactTable, FactTable[TransactionType] IN { "5", "7" } ), dimTable[PartID], FactTable[Length], FactTable[Width] ), "rPartID", dimTable[PartID], "rLength", FactTable[Length], "rWidth", FactTable[Width] ) RETURN GENERATE ( LeftTbl, FILTER ( RightTbl, [rPartID] = dimTable[PartID] && [rLength] = FactTable[Length] && [rWidth] = FactTable[Width] ) )

Re: Need help to convert this LEFT JOIN

Anonymous — Tue, 17 Nov 2020 14:09:11 GMT

Hi wdx223_Daniel ,
It helps me alot but i dont use it as it is.
Make some changes in Dataware House and used your DAX.
Thanks alot.