Forum Discussion
Count where the join fields are equal
Hi All,
I have 2 tables in Power BI which are Bi-Directionally linked. The unique Identifiers have a 1 to 1 relationship. In the first table SurveyTable there are 2,532 records in the second table there are 23,409. In the second table I have a field called primary segments. What I what to do is a count of Primary Segments where the join is equal between the 2 tables. I did this in Access and I know the answer is 2,341. I thought I might have to do a Calculate query something like:-
Calculate(CountRows(Surveydata[ID],Headcount[PrimarySegment]) but this does not work. Any help would be greatly appreciated.
5 Replies
Hi There,
I have a bi-directional join within Power Bi between 2 tables. The headcount table has 23,409 rows of data, the survey data has 2,532. Within the headcount data there is a field called Primary Segment. What I what to do is to do a count of Primary Segments where the join field is equal. I did this within access and the answer was 2,341. I am not sure how to achieve this in powerbi. I thought it might be a calculate function something like:-
Calculate(CountRows(SurveyData[ID],Headcount[PrimarySegment])
However, no luck. Any help would be appreciated.
Kind regards,
James Elwell
Hi Forrestgump,
Based on my test, you could refer to below steps:
Sample data:
Create a measure:
a = COUNTROWS(FILTER('Surveydata',RELATED(Headcount[primary segments])<>BLANK()))Result:
Regards,
Daniel He
try something like this
Measure = CALCULATE(COUNT(Surveydata[ID]),KEEPFILTERS(Headcount[PrimarySegment]))
Hi Forrestgump,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi there,
Unfortunately I haven't been able to find the solution to the problem.
Kind regards,
Forrestgump
