Group by a column and count, but with filters

[Anonymous]

I have a documents table being displayed in my report, then I added a slicer to filter these data using a date range. This is working as expected, but now I need to create a donut chart that should display the summarization.

I've created a calculated table using the SUMMARIZE function like this:

grouped_docs = SUMMARIZE(documents;documents[user_id];"DocCount"; COUNTROWS('documents'))

However, the slicer is ignored.

I also tried to create a measure with the following code:

DocCount = CALCULATE(GROUPBY(documents; documents[user_id]; "GroupByUser"; COUNTROWS(CURRENTGROUP())); ALLSELECTED('documents')) 

But it says that that the GROUPBY needs to have aggregation functions on CURRENTGROUP.

My documents table structure is like this:

document_id (auto increment field)
scanDate
title
user_id

How can I get the count of documents grouped by user_id?

[v-xjiin-msft]

Hi @Anonymous,

=> I added a slicer to filter these data using a date range.

Which column you used for this slicer? scanDate?

=> But it says that that the GROUPBY needs to have aggregation functions on CURRENTGROUP.

You should know that, the CURRENTGROUP function takes no arguments and is only supported as the first argument to one of the following aggregation functions: AverageX, CountAX, CountX, GeoMeanX, MaxX, MinX, ProductX, StDevX.S, StDevX.P, SumX, VarX.S, VarX.P.

Reference: GROUPBY Function (DAX)

So in your scenario, I think you can modify your DocCount measure to this:

DocCount =
CALCULATE (
    GROUPBY (
        documents,
        documents[user_id],
        "GroupByUser", COUNTX ( CURRENTGROUP (), documents[document_id] )
    ),
    ALLSELECTED ( 'documents' )
)

By the way, please share us some sample data (which we can copy and paste directly) and its corresponding expected result. So that we can make some test and provide more proper suggestions.

Thanks,
Xi Jin.

[Anonymous]

Hi @v-xjiin-msft,

Someone else replied my question on Stackoverflow (https://stackoverflow.com/a/49714838/1184708) saying that I don't need DAX to make the Donut chart work, then I tried and it worked.

But I still gave a try at your suggestion and got an error saying that the expression does reference multiple columns and it's not possible to convert multiple columns into a scalar.

Here's a sample generated dataset (CSV) with the same structure as mine:

documents

document_id;title;user_id;scanDate;category_id
1;Maecenas malesuada fringilla est. Mauris eu turpis. Nulla aliquet. Proin;4;03/31/2018;7
2;vel pede blandit congue. In scelerisque;2;02/01/2018;2
3;Nullam vitae diam. Proin dolor. Nulla;2;02/23/2018;8
4;aliquam eu, accumsan sed, facilisis vitae, orci. Phasellus dapibus quam;5;04/03/2018;2
5;congue turpis. In condimentum. Donec at arcu. Vestibulum ante ipsum;5;02/16/2018;8
6;commodo auctor velit. Aliquam nisl. Nulla;3;03/25/2018;2
7;tellus eu augue porttitor interdum. Sed;4;03/23/2018;5
8;Phasellus dapibus quam quis diam. Pellentesque habitant morbi tristique senectus;3;03/29/2018;3
9;nec tellus. Nunc lectus pede, ultrices a, auctor non,;1;02/16/2018;10
10;Duis dignissim tempor arcu. Vestibulum ut eros non enim commodo;3;02/19/2018;7
11;sem, consequat nec, mollis vitae,;2;04/06/2018;9
12;sem magna nec quam. Curabitur vel;3;03/17/2018;7
13;fermentum risus, at fringilla purus mauris a;5;03/28/2018;2
14;cursus. Nunc mauris elit, dictum eu, eleifend nec, malesuada;5;03/15/2018;2
15;ridiculus mus. Aenean eget magna. Suspendisse tristique neque venenatis;2;03/30/2018;4
16;ut ipsum ac mi eleifend egestas.;4;02/24/2018;8
17;malesuada fames ac turpis egestas. Fusce aliquet magna;4;03/09/2018;1
18;cursus et, magna. Praesent interdum ligula;5;03/01/2018;5
19;cursus. Nunc mauris elit, dictum eu, eleifend nec, malesuada ut,;4;04/05/2018;1
20;est. Mauris eu turpis. Nulla aliquet.;3;02/12/2018;2
21;venenatis lacus. Etiam bibendum fermentum metus. Aenean sed pede;4;03/23/2018;1
22;id, blandit at, nisi. **bleep** sociis;3;02/25/2018;3
23;consectetuer euismod est arcu ac;1;02/19/2018;4
24;convallis est, vitae sodales nisi magna;1;03/06/2018;2
25;non, sollicitudin a, malesuada id, erat. Etiam;5;02/20/2018;10
26;"ultrices posuere cubilia Curae; Donec tincidunt. Donec";3;02/15/2018;8
27;dapibus quam quis diam. Pellentesque habitant;1;03/08/2018;8
28;volutpat ornare, facilisis eget, ipsum. Donec;3;02/08/2018;8
29;Aenean massa. Integer vitae nibh. Donec est mauris, rhoncus;1;04/08/2018;10
30;Nunc ullamcorper, velit in aliquet lobortis,;3;02/27/2018;8
31;nec, mollis vitae, posuere at, velit. Cras;5;04/06/2018;7
32;turpis egestas. Fusce aliquet magna;2;02/03/2018;2
33;pede ac urna. Ut tincidunt vehicula risus. Nulla eget;5;03/05/2018;9
34;Nam interdum enim non nisi. Aenean eget;5;03/29/2018;10
35;turpis. Aliquam adipiscing lobortis risus. In mi pede, nonummy;1;03/07/2018;5
36;ipsum porta elit, a feugiat tellus lorem;2;02/04/2018;1
37;lobortis tellus justo sit amet nulla. Donec;4;03/31/2018;4
38;nec tempus mauris erat eget ipsum. Suspendisse sagittis. Nullam vitae;5;04/03/2018;9
39;Nunc pulvinar arcu et pede. Nunc sed orci lobortis augue;4;03/02/2018;8
40;risus a ultricies adipiscing, enim mi tempor lorem, eget mollis;1;03/20/2018;9
41;habitant morbi tristique senectus et;5;03/17/2018;7
42;id nunc interdum feugiat. Sed nec metus facilisis;2;02/21/2018;1
43;cursus non, egestas a, dui. Cras pellentesque. Sed dictum.;5;03/13/2018;5
44;et tristique pellentesque, tellus sem mollis dui, in sodales;5;02/27/2018;8
45;tincidunt aliquam arcu. Aliquam ultrices;1;03/24/2018;8
46;Donec feugiat metus sit amet ante. Vivamus;4;03/31/2018;9
47;risus. Nulla eget metus eu erat;4;04/01/2018;10
48;dolor. Fusce feugiat. Lorem ipsum;4;04/05/2018;8
49;Morbi accumsan laoreet ipsum. Curabitur consequat, lectus sit;3;02/16/2018;5
50;orci luctus et ultrices posuere;1;03/05/2018;10
51;lectus justo eu arcu. Morbi sit;5;02/19/2018;4
52;nulla ante, iaculis nec, eleifend non, dapibus rutrum,;4;03/18/2018;5
53;Nunc quis arcu vel quam;4;03/03/2018;5
54;Duis ac arcu. Nunc mauris. Morbi non;5;04/05/2018;2
55;turpis. In condimentum. Donec at arcu. Vestibulum;4;03/27/2018;3
56;tellus. Suspendisse sed dolor. Fusce mi lorem,;2;04/08/2018;7
57;luctus aliquet odio. Etiam ligula tortor, dictum eu, placerat eget,;3;03/07/2018;6
58;dictum. Phasellus in felis. Nulla tempor;1;03/08/2018;4
59;tellus lorem eu metus. In lorem. Donec elementum, lorem ut;5;02/18/2018;5
60;mattis. Integer eu lacus. Quisque imperdiet,;1;02/01/2018;9
61;dis parturient montes, nascetur ridiculus mus.;4;04/04/2018;1
62;ridiculus mus. Proin vel arcu eu odio tristique;3;02/18/2018;4
63;sit amet lorem semper auctor. Mauris vel;2;02/27/2018;8
64;elit, pharetra ut, pharetra sed, hendrerit a, arcu.;2;03/02/2018;7
65;per conubia nostra, per inceptos hymenaeos. Mauris ut quam;1;03/11/2018;5
66;tortor, dictum eu, placerat eget,;5;03/15/2018;10
67;euismod enim. Etiam gravida molestie;3;03/22/2018;8
68;in consequat enim diam vel arcu.;2;03/11/2018;5
69;consequat nec, mollis vitae, posuere at,;4;02/07/2018;1
70;amet ante. Vivamus non lorem vitae odio sagittis semper. Nam;3;02/07/2018;9
71;Duis cursus, diam at pretium aliquet, metus urna convallis erat,;2;04/09/2018;2
72;Aenean euismod mauris eu elit.;5;03/04/2018;1
73;est ac mattis semper, dui lectus rutrum urna, nec;2;02/06/2018;1
74;quis, pede. Praesent eu dui. **bleep** sociis natoque;5;04/09/2018;9
75;vulputate, lacus. Cras interdum. Nunc sollicitudin commodo;1;04/06/2018;2
76;ac tellus. Suspendisse sed dolor. Fusce mi lorem,;4;04/09/2018;7
77;Vestibulum accumsan neque et nunc. Quisque ornare tortor;3;02/23/2018;2
78;vel, venenatis vel, faucibus id, libero.;4;03/24/2018;3
79;sem mollis dui, in sodales elit erat vitae;4;03/04/2018;6
80;diam luctus lobortis. Class aptent taciti;4;02/18/2018;3
81;ornare, facilisis eget, ipsum. Donec;5;04/07/2018;10
82;facilisis facilisis, magna tellus faucibus leo, in lobortis tellus justo;3;03/23/2018;3
83;tempor arcu. Vestibulum ut eros non enim commodo hendrerit.;1;02/23/2018;7
84;Cras pellentesque. Sed dictum. Proin eget;1;03/06/2018;6
85;amet diam eu dolor egestas rhoncus. Proin;5;02/27/2018;1
86;sed pede nec ante blandit;1;03/03/2018;6
87;ornare sagittis felis. Donec tempor, est ac;3;02/08/2018;4
88;eu nulla at sem molestie;4;02/26/2018;7
89;vel, venenatis vel, faucibus id, libero.;2;02/26/2018;3
90;velit. Cras lorem lorem, luctus ut, pellentesque eget,;5;03/07/2018;2
91;malesuada fringilla est. Mauris eu turpis. Nulla aliquet. Proin;3;04/06/2018;1
92;neque venenatis lacus. Etiam bibendum;1;02/18/2018;4
93;aliquam eros turpis non enim. Mauris;2;02/05/2018;10
94;Sed molestie. Sed id risus quis;1;02/28/2018;9
95;dictum mi, ac mattis velit justo nec ante.;5;03/24/2018;7
96;nulla. Integer vulputate, risus a ultricies adipiscing, enim mi tempor;1;02/03/2018;1
97;mollis. Phasellus libero mauris, aliquam eu, accumsan sed, facilisis vitae,;1;03/15/2018;9
98;faucibus leo, in lobortis tellus justo sit amet nulla. Donec;2;02/01/2018;4
99;vel est tempor bibendum. Donec felis orci, adipiscing non, luctus;4;03/26/2018;5
100;convallis in, cursus et, eros. Proin ultrices. Duis volutpat;5;02/23/2018;9

users

user_id;name
1;Stuart Rutledge
2;Len Hickman
3;Megan Fleming
4;Berk Parks
5;Iris Pitts

I need to create the donut chart that shows how many documents each user_id is associated with, but I have two context filter: scanDate (date range) and category_id.

[v-xjiin-msft · Accepted solution]

Hi @Anonymous,

I have made a mistake. GROUPBY() function returns a table. So we can't use it in a measure. Try to create a new calculated table with GROUPBY(), also no need to add CALCULATE() and ALLSELECTED('documents'):

DocCount = 
    GROUPBY (
        documents,
        documents[user_id],
        "GroupByUser", COUNTX ( CURRENTGROUP (), documents[document_id] )
    )

By the way, there's an easier way to count documents for each user_id with DAX expression. We can use ALLEXCEPT() function to make groups in a measure. So we don't need to create a new table.

Count documents =
CALCULATE (
    COUNT ( documents[document_id] ),
    ALLEXCEPT ( documents, documents[user_id] )
)

Thanks,
Xi Jin.