Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

left join in tabular model

I Have a situation where cross join and left joins are involved.I want to implement the below query in tabular model.I have managed till cross join using Dax -Cross join (tableI,tableT) and joined all other table A,P,O,T but how to join the last two table(T,PH) which join on the result set of cross join (tableI,tableT)  based on two key column -TH.colI= Main.colI , TH.colT=d.colT .

Query need to be implemented in tabular model.

Easiest way is to implement using creation of views.

Select I*,A.*,P.*,O.*,T.*,d.colT,Th.colX,PH.ColPH from C WITH (NOLOCK)
LEFT JOIN I WITH (NOLOCK) ON C.colC= I.colI
LEFT JOIN A WITH (NOLOCK) ON I.colI = A.colA
LEFT JOIN P WITH (NOLOCK) ON I.colI = P.colP
LEFT JOIN O WITH (NOLOCK) ON I.colI = O.colO
LEFT JOIN T WITH (NOLOCK) ON T.colT= P.colP
and T.colT IN
( 'A','B','C')
cross join (SELECT DISTINCT colT FROM T WHERE colT IN
('A'
,'B'
,'C'
,'D'
,'E'
,'F'
))d

 

)Main

left JOIN T AS TH WITH (NOLOCK) ON TH.colI= Main.colI and TH.colT=d.colT
left join PH WITH (NOLOCK) ON PH.colI=Main.colI

 

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support

    Hi Anonymous,

     

    Here I think we can use NATURALLEFTOUTERJOIN and CALCULATETABLE to work on it. Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

     

    Regards,

    Frank