Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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
hi
thanks a lot , it works correctly but if i need to count just a distinct id
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] 😞
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
Thank you so much !
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
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")
Proud to be a Super User!
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 😞
there isnt an inner join in power BI just a relationship
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?
Proud to be a Super User!
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
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?
Proud to be a Super User!
the relationship is an inner join.
Proud to be a Super User!
User | Count |
---|---|
82 | |
76 | |
71 | |
69 | |
54 |
User | Count |
---|---|
105 | |
100 | |
91 | |
79 | |
68 |