Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Boycie92
Resolver I
Resolver I

Distinct Headcount Calculated Column

 

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,

 

1 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,

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@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,

 

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,

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.