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