Count where the join fields are equal

[Forrestgump]

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.  

[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

[Forrestgump]

Hi there,

Unfortunately I haven't been able to find the solution to the problem.

Kind regards,

Forrestgump

[Forrestgump]

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-msft]

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]

try something like this

Measure = CALCULATE(COUNT(Surveydata[ID]),KEEPFILTERS(Headcount[PrimarySegment]))