cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
richardhiers
Frequent Visitor

De-duplicatd headcount

I'm looking for suggestions to view de-duplicated results for university admissions application data for a selected period.

 

Most applicants apply for one degree program at a time. Some prospects though apply to be admitted to two programs simultaneously.  My fact table has a transaction for each application/program, and my PBI reports show the number of applications for whatever period is selected in the slicer.

 

Is there a way to filter this down to a de-duplicated or distinct headcount within the report? I'm looking for results that include only one instance of every applicant who applied in the selected period, and for those who applied to more than one program, the instance retained would be based on a ranking of the degrees: those with more required credit hours prefered to lower credit hour degrees. For example:

 

Prospect    Degree Program          Priority   In Report?
John Doe    Degree A (100 hours)    1          Yes
John Doe    Degree B (80 hours)     2          No

So if John Doe applied for thsese two programs in 2017 Q1, the 2017report would show only one. However, if John Doe applied for these two in 2017, but an earlier degree in 2013, a report showing year over year would have one result for John in 2013 and another in 2017.

 

Or would this best be done with a second, de-duplicated source fact table?

 

Thanks

Richard

 

 

 

5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: De-duplicatd headcount

@richardhiers,

Could you please share sample data of your table and post expected result in  table format?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
richardhiers
Frequent Visitor

Re: De-duplicatd headcount

Yes, I can do that. Can you tell me the best way to share it on the forum? The example I gave initially was kind of clunky using the insert code option.

Thanks

Richard

Moderator v-yuezhe-msft
Moderator

Re: De-duplicatd headcount

@richardhiers,

You can publish your file to OneDrive and post shared link of the file here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
richardhiers
Frequent Visitor

Re: De-duplicatd headcount

Moderator v-yuezhe-msft
Moderator

Re: De-duplicatd headcount

@richardhiers,

I am not very clear about the logic you use to get expected result, could you please explain the process based on the sample data you shared with me? And do you only use Sample Human Readable Dataset in the Excel?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors