topic Re: Left join with three tables in DAX Commands and Tips

Left join with three tables

Trodo737 — Fri, 28 Jun 2024 07:19:39 GMT

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.

Re: Left join with three tables

bhanu_gautam — Fri, 28 Jun 2024 11:08:34 GMT

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

Re: Left join with three tables

xifeng_L — Fri, 28 Jun 2024 11:26:34 GMT

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~

Re: Left join with three tables

Trodo737 — Fri, 28 Jun 2024 11:54:20 GMT

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.

Re: Left join with three tables

Trodo737 — Fri, 28 Jun 2024 13:11:43 GMT

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.

Re: Left join with three tables

xifeng_L — Fri, 28 Jun 2024 15:28:20 GMT

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~

Re: Left join with three tables

bhanu_gautam — Fri, 28 Jun 2024 17:13:43 GMT

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"
)