Power BI - DAX Measure Direct Query

Question

I'm trying to create a measure that give me the SUM of value of a table A.

And the query that I have in SQL is:

SELECT SUM (VALUE) 
FROM A 
INNER JOIN B on A.ID = B.T_ID 
INNER JOIN on B.P_ID = C.ID 
INNER JOIN D on C.P_L_ID = D.ID 
INNER JOIN E on D.P_D_ID = E.ID 
INNER JOIN A_2 on C.T_ID = A_2.ID 
INNER JOIN F on E.P_G_M_ID = F.ID 
INNER JOIN G on F.P_G_ID = F.ID 
WHERE A_2 = "XPTO" 
AND   D <> 2

All tables are link in dataset model but it doesn't work.

There any chance to do it by DAX ?

Thanks

Answer 1

You need to create a measure column with a dex command like below. Basically, you will need to use nested NATURALINNERJOIN functions as a table for FILTER of SUMX function and use your filters with an AND function.

MeasureColumn = SELECTCOLUMNS(A,"newcolumn", sumx(FILTER(NATURALINNERJOIN(G,NATURALINNERJOIN(F,NATURALINNERJOIN(E,NATURALINNERJOIN(D,NATURALINNERJOIN(C,NATURALINNERJOIN(A,B)))))), AND(A[A_2]="PXTO",D[ID]<>2)),A[value]))