Count Unique where join field is equal

[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 165,600. 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. Please note, within the survey data table there are multiple rows per ID. Therefore, the ID is not unique within this table. The id is however unique within the headcount table. 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

 

[Matt Allington]

Try this

CALCULATE(distinctcount(headcount[primarysegment]),SurveyData)

 

[forrestgump]

Hi Matt,

Thanks for that, I actually used:-

CALCULATE(distinctcount(headcount[ID]),SurveyData)

It works which is fantastic. I was actually in your online classes(James from UK). Much appreciated!!!!!!!