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
v-danhe-msftMicrosoft Employee
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
- Stachu
Community Champion
try something like this
Measure = CALCULATE(COUNT(Surveydata[ID]),KEEPFILTERS(Headcount[PrimarySegment]))
- v-danhe-msft
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
