Count of Counts

[JonathanGibbs]

I have a 'Count of counts' DAX query.

Sample table tVisits:
Year  Person SiteVisited
2021 A          S1
2021 A          S2
2021 A          S1
2021 B          S3
2021 C          S2
2021 C          S4
2021 C          S5
2022 A          S2
2022 B          S2
2022 C          S3
2022 C          S4

In Year 2021, Person A visited 2 distinct sites, Person B 1 site, Person C 3 sites.
In Year 2022, Person A visited 1 site, Person B 1 site, Person C 2 sites.

I want to get, for a given year selected, the count of people attending exactly one site, exactly 2 sites, 3 sites etc., and present that as a bar chart.

So in Year 1, it's
SitesVisited CountOfPeople
1                  1
2                  1
3                  1

in Year 2 it's
SitesVisited CountOfPeople
1                  2
2                  1
3                  0

I think this is a DAX SUMMARIZE issue. I've got as far as creating a table that will be a source for the bar chart, defined as

tSummary =
SUMMARIZE(
   SUMMARIZE(
      tVisits,
      tVisits[Person],
      "DistinctSites",
      DISTINCTCOUNT(tVisits[SiteVisited])
   ),
   [DistinctSites],
   "CountVisitors",
   COUNT(tVisits[Person])
)

But this looks at the whole of tVisits, not just the selected year.

How do I get a filter on Year into this somehow?

[JonathanGibbs · Accepted solution]

@Greg_Deckler  No, that's the point - any slicer or filter settings applied to the underlying table (tVisits) don't propagate through to the derived table (tSummary).

But I think I've found the answer myself - use multiple 'group by' clauses in the SUMMARIZE instructions, then apply the same slicers/filters to the derived table as to the source. So I can pass through the Year like this:

tSummary =
SUMMARIZE(
   SUMMARIZE(
      tVisits,
      tVisits[Person],
      tVisits[Year],
      "DistinctSites",
      DISTINCTCOUNT(tVisits[SiteVisited])
   ),
   [DistinctSites],
   [Year],
   "CountVisitors",
   DISTINCTCOUNT(tVisits[Person])
)

Not sure if this is the most elegant solution, but it works..

[Greg_Deckler]

@JonathanGibbs Right, that's was the other route to go, just add Year summarization. Was not clear that you were trying to create a summary table.

[Greg_Deckler]

@JonathanGibbs Use a Year slicer?