Left join with three tables

[Trodo737]

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(t1.cal7 AS BIGINT)), '### ### ### ###') AS 'Total'
     ,t3.col5
  FROM T2 t2
  LEFT JOIN T1 t1      ON t2.IDnr = t1.IDnr
  LEFT JOIN T3 t3      ON t2.identifiernr= t3.identifiernr
  WHERE t3.col4 LIKE 'LastReport'  
  GROUP BY t2.col1, t3.col5

Both t2.col1 and t3.col5 have nvarchar as datatype. I want to show result as a barchart in Power BI report where the Legend is t3.col5, t2.col1 is on X-axis and  Total on Y-axis.

Any help is appreciated.

[xifeng_L]

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]

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]

@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~

[bhanu_gautam]

@Trodo737 , You can do it using measures in Power BI but make sure relation between T2 and T3 , and T1 and T3 is established ,

Create a measure using

Total =
VAR FilteredT3 =
FILTER(
T3,
T3[col4] = "LastReport"
)
RETURN
SUMX(
FILTER(
T1,
T1[IDnr] IN VALUES(T2[IDnr])
),
T1[cal7]
)

FormattedTotal =
FORMAT(
[Total],
"### ### ### ###"
)

Then you can use Formatted Total as value in bar chart visual

[Trodo737]

Thanks @bhanu_gautam 

As I mentioned there is no relationship between T1 and T3 but each of them has relationship to T2 on different columns(IDnr and identifiernr).

I tested your suggested dax code, it works but it shows the same result(for SUMX) for all rows(without VAR FilteredT3). When I run SQL query getting different nummers for each rows:

In both t2.col1 and t3.col5 there are other rows, this is just an example of the result I'm trying to get with dax code.

[bhanu_gautam]

@Trodo737 , You can try this measue if still there is still issue can you share sample data

dax
Total =
CALCULATE(
FORMAT(
SUMX(
T1,
T1[cal7]
),
"### ### ### ###"
),
T3[col4] = "LastReport"
)