Count of users by date

[mark_carlisle]

I have query (LoggedTickets) which shows the date (CallDate) of the call along with the a unique name of the person (UserName) taking the call, there are other columns in this data but they are not relevant to the question.

What I want to do is calculate the total number of distinct UserName's that have taken a call on a given CallDate, I'll call this column UserNameCount. A simplified example follows.

CallDate	UserName	UserNameCount
23 August 2018	1	17
23 August 2018	2	17
23 August 2018	2	17
...	...	17
24 August 2018	5	18
24 August 2018	1	18
...	...	18

The above table shows that 17 different UserName's took a call on 23/08/2018 and 18 on the 24/08/2018. 

I believe I need to do all this is the query editor (Power Query) because I then reference the LoggedTickets query to create another one (LoggedTickets_Anon), which I group by CallDate so the data is anonoymised. I do this as I use Row Level Security and I want people to see their figures and then an average of what people in their team are doing for comparison. An example of how I would like the LoggedTickets_Anon query follows.

CallDate	TicketsLogged	UserNameCount
24 August 2018	384	18
23 August 2018	391	17

TicketsLogged is simply a Count of the rows and UserNameCount would be a Max aggregation when grouping. 

[rajulshah · Accepted solution]

Hello @mark_carlisle,

As per my understanding of the requirements, you can create a summarized table using following query:

Table = SUMMARIZE(LoggedTickets,CallDate,"TicketsLogged",COUNTROWS(LoggedTickets),"UsernameCount",DISTINCTCOUNT(LoggedTickets[UserName]))

Hope this helps. Let me know if this doesn't work.

[mark_carlisle]

That's great. Thanks for this.