Forum Discussion

Trodo737's avatar
Trodo737
Frequent Visitor
2 years ago

Left join with three tables

I have three tables, T1, T2 and T3. T2 has relation with T1 and T3 but T1 and T3 don't have any relation. How can I convert the following SQL to DAX:   SELECT t2.col1 ,FORMAT(SUM(CAST...
xifeng_L's avatar
xifeng_L
Icon for Super User rankSuper User
2 years ago

Hi Trodo737 

 

Please make sure that PowerBI is modelling the relationships correctly, both T1 and T2 and T3 and T2 should be one-to-many relationships.

 

Then please try the following queries or new tables expression.

 

DAX Query:

EVALUATE
SUMMARIZE (
    CALCULATETABLE ( 'T2', 'T3'[col4] = "LastReport" ),
    'T2'[col1],
    'T3'[col5],
    "Total", SUM ( 'T1'[col7] )
)

 

New Table:

TableName = 
SUMMARIZE (
    CALCULATETABLE ( 'T2', 'T3'[col4] = "LastReport" ),
    'T2'[col1],
    'T3'[col5],
    "Total", SUM ( 'T1'[col7] )
)

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

Trodo737's avatar
Trodo737
Frequent Visitor
2 years ago

Thanks xifeng_L 
I'm totaly new in DAX. I checked it and the relationships are one-to-many:

T2 -> T1  is one-to many

T2 -> T3  is one-to-many

and T1 and T3 doesn't have any relationship.

When I use the dax code you suggested getting this error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

  • xifeng_L's avatar
    xifeng_L
    Icon for Super User rankSuper User
    2 years ago

    Trodo737 

     

    I'm guessing you're using either the measure or the calculated column option? Both of these options need to return scalar values, whereas I gave you the expression to create a table. Obviously, a table can't fit into a cell. So you should use the "New Table" option.

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~