Forum Discussion

Mariam1991's avatar
Mariam1991
Helper II
9 years ago
Solved

inner join

Hi,  i need to join 2 tables in order to calculate a meaure in power BI  i have 2 tables A and table B my aim is to add a measure in table A  in sql : select count(A.ID) from A  inner join B on ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Mariam1991,

     

    Could you try the formula below to see if it works in your scenario? :smileyhappy:

    New Column In Table A = COUNTROWS ( FILTER ( RELATEDTABLE ( 'TableB' ), 'TableB'[status] = "payed" ) )

     

    Regards

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

Hi Mariam1991,

 

Could you try the formula below to see if it works in your scenario? :smileyhappy:

New Column In Table A = COUNTROWS ( FILTER ( RELATEDTABLE ( 'TableB' ), 'TableB'[status] = "payed" ) )

 

Regards

  • Mariam1991's avatar
    Mariam1991
    Helper II
    9 years ago

    hi 

    thanks a lot , it works correctly but if i need to count just a distinct id

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

      Hi Mariam1991,

       

      Could you try the formula below to see if it works? :smileyhappy:

      New Column In Table A =
COUNTROWS (
    FILTER (
        RELATEDTABLE ( 'TableB' ),
        'TableB'[status] = "payed"
            && 'tableB'[DATE_str] = 'TableA'[Date]
    )
)

       

      Regards

    • Mariam1991's avatar
      Mariam1991
      Helper II
      9 years ago

      it doesn't work  

      New Column In Table A = COUNTROWS ( FILTER ( RELATEDTABLE ( 'TableB' ), 'TableB'[status] = "payed" && 'tableB'[DATE_str] = 'TableA'[Date] ) ) it doesnt give me the possibility to add  =tableA[DATE] :(

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

        Hi Mariam1991,

         

        A little weird, the formula works fine on my test.

         

        So could you try the formula below to see if it works? :smileyhappy:

        New Column In Table A =
VAR dateA =
    MAX ( 'TableA'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            RELATEDTABLE ( 'TableB' ),
            'TableB'[status] = "payed"
                && 'tableB'[DATE_str] = dateA
        )
    )

         

        Regards