Forum Discussion
Grouping..
Anonymous
In Power Query, select both the Date and the User Column pressing CTRL, right-click, choose Remove Duplicates. You will get rid of any duplicates within Date and User and retain all other columns as well.
Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
distinctcount calculated again for grand total
if you want sum you need to try like
sumx(values(Table[user]), distinctcount(Table[Where]))
User or department is grouping used in visual
Grand total - https://www.youtube.com/watch?v=ufHOOLdi_jk
Thanks for the message, I gave your SUM a try but I think the issue is that I am reverting to a date field to support my question.
Original Q:
To count the number of times a worker has clocked into a building, but removing any duplication within the same date.
Sample Data (within Excel):
Desired Outcome (as illustrated within Excel):
Current view within PowerBi using the 'Grouping Function' along with the 'CountDistinct' against 'User' which gives me the correct result, but the remaining columns are lost (User, Department, Where):
I followed this tutorial to achieve the above for context:
https://www.youtube.com/watch?v=7l4dYEjmgqA
Is there a way to tie in my original columns sets, so I can apply drill downs as required?
Anonymous
In Power Query, select both the Date and the User Column pressing CTRL, right-click, choose Remove Duplicates. You will get rid of any duplicates within Date and User and retain all other columns as well.This works! Thank you so much!