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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mark_carlisle
Advocate IV
Advocate IV

Count of users by date

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.

 

CallDateUserNameUserNameCount
23 August 2018117
23 August 2018217
23 August 2018217
......17
24 August 2018518
24 August 2018118
......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.

 

CallDateTicketsLoggedUserNameCount
24 August 201838418
23 August 201839117


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

 

1 ACCEPTED SOLUTION
rajulshah
Super User
Super User

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.

View solution in original post

2 REPLIES 2
rajulshah
Super User
Super User

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.

That's great. Thanks for this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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