Need help to convert this LEFT JOIN

[Anonymous]

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

[amitchandak]

@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

[Anonymous]

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

[wdx223_Daniel · Accepted solution]

@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]
        )
    )

[Anonymous]

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.

[amitchandak]

@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