Distinct Headcount Calculated Column

[Boycie92]

Hi,

I was wondering if someone can help me.

I am trying to create a calculated column that will count the distinct values of my Person ID column.

To explain my model. CSV Reports are ran every month and dropped into a folder. I have used Power BI to merge all files into one query using the: connect to Folder function.

In order to filter the reports by date each file has a date column added to it. For Example Date: 1 November 2016. This appears on every record for that report.

I essentially need a formula that will count the distinct headcount for each month. However there may be multiple references of a single person ID in each month. I need the formula to count the first one but ignore the rest.

I was able to use this formula for positions and it works. Positions = CALCULATE (DISTINCTCOUNT ('Reports'[Position ID]))

However this is because every position has a UNIQUE ID. Is there a way to adapt it in order to deal with duplicated values for Person ID?

Thanks,

[v-sihou-msft]

@Boycie92

Based on your description, no matter you want to distinct count Person ID or Position ID, you can always use ALLEXCEPT() to make your calculation group on "Month". The formula can be like:

= CALCULATE (DISTINCTCOUNT ('Reports'[Position ID]),ALLEXCEPT('Reports','Reports'[Month]))

Regards,

[Boycie92 · Accepted solution]

Hi @v-sihou-msft

Thank for your reply.

Your solution provides the same result as my Position ID calculation.

However this is incorrect. There are more positions than there are people. This is because of employees that have multiple positions.

I needed a solution that was flexible enough to identify the duplicate PersonID by labelling the first instance as 1 and then any other entries within that month were marked as 0.

I have found a solution that works. However I was wondering why it works? and if the solution can be simplified into less steps.

Steps:

1) Add index column in Power Query –Unique Row Identifier

2) Add Calculated Column in Power Pivot that merged together PersonID and Date:

Unique ID = 'Reports'[PersonID] & "-" & 'Reports'[Date].[Date]

3) Identified the duplicates of PersonID within each month

DupsID = IF(Reports'[Index]=MAXX(FILTER('Reports','Reports'[Unique ID]=EARLIER('Reports'[Unique ID])),'Reports'[Index]),"No","Yes")

4) Created calculated column that counts headcount

Headcount = CALCULATE(DISTINCTCOUNT('Reports'[PersonID]),'Reports'[DupsID]="No")

Thanks for the help,