inner join

[Mariam1991]

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 

[v-ljerr-msft · Accepted solution]

Hi @Mariam1991,

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

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

Regards

[Mariam1991]

hi 

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

[Mariam1991]

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]

Hi @Mariam1991,

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

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

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

Regards

[Mariam1991]

Thank you so much !

[v-ljerr-msft]

Hi @Mariam1991,

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

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

Regards

[vanessafvg]

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]

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 😞

[Mariam1991]

there isnt an inner join in power BI just a relationship 

[vanessafvg]

Power BI is not the same as sql, creating a relationship has the same affect as an inner join, so once you have created the relationship(join) it should allow you to create the measure, have you tried and if so what error are you getting?

[Mariam1991]

okey ! there is no error but in result we observe that it doesn't take the join in consideration , like we calculate the count(id ) in table A without the relationship with table B ... between thanks for your feedback 

[vanessafvg]

Can you show me how you wrote the measure?

also can you show me the screen shot of the join?

do you want to count unique id's or all the rows?

[vanessafvg]

the relationship is an inner join.