Forum Discussion
Grouping..
Hi there,
I am trying to use the 'countdistinct' function within Bi - which seems to work in some capacity, but the totals don't add up which is why I've tried to head down the grouping route. Whenever I group the data together, I lose sight of all remaining columns.
I 'referenced/copied' my original data set, applied the grouping steps however when merging the 2 tables together my numbers don't add up.
Example within the below excel data set being used, overall i'm looking to count the number of times a worker has clocked into a building but removing any duplication within the same date.
Would be keen to get some thoughts - not sure if even heading down the grouping route will help?
I am relatively new to Bi š
Thanks.
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.
4 Replies
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.
