Forum Discussion

Mariam1991's avatar
Mariam1991
Icon for Helper II rankHelper II
9 years ago

JOIN 3 tables

Hi everyone; I have 3 tables i need to join its to calculte distinct id  in table A distinctcount(tableA[id]) but the juncture will be between 3 tables  as below: tableA[id]=max(tableB[id]) tableB...
Need Help
Mariam1991's avatar
Mariam1991
Icon for Helper II rankHelper II
9 years ago

i don't know how but my aim is to calculate distinct id in table A taking in consideration the juncture between 3 tables 

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Mariam1991,

 

Could you post your table structures with some sample data and your expected result? So that we can better assist on this issue. :smileyhappy:

 

Regards

  • Sunkari's avatar
    Sunkari
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    @Mariam1991: Yes you please provide more details, I did not understand your requirement properly.

     

     

    Thanks,

    Sunkari

     

     

  • Mariam1991's avatar
    Mariam1991
    Icon for Helper II rankHelper II
    9 years ago

    HI

    thank for your help , i have 3 table A, B and C  the meaure need a juncture between them as you will see total souscrpition by sex , by group age  are in table C but the measure i should calculate distinct id in table A , the juncture :

    inner join table B on max(tableB[id])=tableA[id]

    inner join table C on tableB[id]=tableC[the structure of 3 tablesid]total souscription by group age but the calcul is false :(

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi Mariam1991,

       

      Could you also post some sample data for the three tables and the expected result against the sample data? :smileyhappy:

       

      Regards

      • Mariam1991's avatar
        Mariam1991
        Icon for Helper II rankHelper II
        9 years ago

        Hi,

        To facilitate a little my problem, i need to transform a SQL query into a measure in power BI , i have 3 tables,i would calculate count Id in table A by age or sexe which are in table C the problem there is no direct relationship between them , i have to go through table B 

        table A (ID)>>table B(ctr_id)>> tablec(id)

        my queqy is : 

        select C1.sexe, count (A1.ID) from table A A1

        inner join table B B1 on B1.ctr_id=A1.ID

        inner join table C C1 on C1.id=B1.id

        where A1.status='payed' and B1.id=  (select max(id) from table B B2 where B2.ctr_id=A1.ID)

        group by C1.sexe

         

        Really im blocked, thank your for your help