Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |