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(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.

6 Replies

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

    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's avatar
      Trodo737
      Frequent Visitor
      2 years ago

      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's avatar
        bhanu_gautam
        Icon for Super User rankSuper User
        2 years ago

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

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