Forum Discussion
Count of Counts
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?
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..
3 Replies
- Greg_Deckler
Community Champion
JonathanGibbs Use a Year slicer?
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.
