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

      • Mariam1991's avatar
        Mariam1991
        Helper II
        9 years ago

        Thank you so much !