topic Count of Counts in DAX Commands and Tips

Count of Counts

d2hughes — Fri, 19 Nov 2021 16:09:32 GMT

Hello,

I am going to try this again, last time I posted it was marked as spam. I think it was because it was my first post and I included a link to a sample .pbix from my OneDrive.

I need to make a visualization of the counts of how many times people visited. The visits are represented like this:

ContactID	Date
1	1/1/2021
1	1/2/2021
2	1/1/2021
3	1/1/2021
3	1/2/2021
3	1/3/2021
4	1/1/2021
5	1/1/2021
5	1/2/2021
5	1/3/2021
5	1/4/2021
5	1/5/2021
6	1/1/2021
6	1/2/2021
7	1/1/2021
8	1/1/2021
9	1/1/2021
9	1/2/2021
9	1/3/2021

I have a measure that will give me the number of visits by ContactID:

Visits = COUNTROWS(FILTER('Visits',Visits[ContactID] = Visits[ContactID]))

When viewed on a table it looks correct:

ContactID	Visits
1	2
2	1
3	3
4	1
5	5
6	2
7	1
8	1
9	3

This is where I get lost. Somehow I need to get it to count these counts which could be represented like this:

Visits	Count of Visitors
1	4
2	2
3	2
4	0
5	1

Which lastly needs to be visualized like this:

Any help would be greatly appreciated!

Thank you!

David

Re: Count of Counts

VahidDM — Mon, 22 Nov 2021 07:19:22 GMT

Hi d2hughes

Try this code to create a new table:

Table = GROUPBY( SUMMARIZE( Visits, Visits[ContactID], "Visits", COUNTROWS( FILTER( 'Visits', Visits[ContactID] = Visits[ContactID] ) ) ), [Visits], "Count of Visitors", COUNTX( CURRENTGROUP(), [ContactID] ) )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Count of Counts

d2hughes — Mon, 22 Nov 2021 15:58:04 GMT

Yes this looks correct! I made this a simple sample but it should translate to the actual data I am working on.

Thank you so much!

Re: Count of Counts

d2hughes — Mon, 22 Nov 2021 16:57:19 GMT

Sorry, I forgot one caveat. I need to be able to slice it by the date column. This table works fine for the whole data set but it is unaffected by a slicer on the base table's date. Any ideas?