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 B.id=A.ID 

where B.status='payed'

Thank you in advance 

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

13 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    create a relationship if there isn't already between table table a id to table b id in the relationship view

     

    measurename = calculate(countrows(['table A'), 'Table B'[Status] = "payed")

    • Mariam1991's avatar
      Mariam1991
      Helper II
      9 years ago

      thanks but i dont need a relation but an inner join between 2 table 

      more than that i do the same measure and it doesn't work :(

  • 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