Count of Counts

[d2hughes]

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

[d2hughes]

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!

[VahidDM · Accepted solution]

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/

[d2hughes]

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?