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
