Help Understanding Aggregation Awareness

[brussom]

Hey all,
I am trying to follow along with this blog post I found about filtered aggregations in a large dataset
https://dax.tips/2019/10/30/creative-aggs-part-iv-filtered-aggs/
Setting up a test environment to replicate Steps 1-4, i created a Numbers table with 100,000 rows in it....
One Table in in DIrectQuery mode with numbers >=1000
One Table in DirectQuery mode with numbers <1000
One Table designated the "Aggregate" table in Import Mode with numbers <1000 (Duplicate of the above table).
My understanding from the reading blog post, is that any queries that satisfy the aggregations configured by going to "Manage aggregations" should be detected and run through the Import table...and anything else should fall back to the smaller DQ table.....and when running the query through DAX Studio, I can see that it does exactly what I it should, except that the resulting query is always coming back as a SUM instead of a count.

'Aggregate' table does not actually have any aggregations...simply because I am trying to follow the blog as close as I can.
'Aggregate' table has been configured to look for a Count of 'N' on the 'NumbersUnder1k' table.

Running the DAX Query
EVALUATE 
SUMMARIZECOLUMNS("Count",COUNT('NumbersUnder1k'[N]))

through DAX Studio, shows that it hit the aggregation table just like I expect
{ "table": "NumbersUnder1k",
"matchingResult": "matchFound",
"mapping": {
"table": "NumbersImport_Agg"
},
"dataRequest": [
{
"aggregation": "Count",
"table": "NumbersUnder1k",
"column": "N",
"mapping": {
"table": "NumbersImport_Agg",
"column": "N"
} } ]}

But the resulting scan always comes back as a SUM:
SELECT
SUM ( 'NumbersImport_Agg'[N] )
FROM 'NumbersImport_Agg';

I have been over everything I can think of, set every field I can find to "Don't Summarize" and I can't find anywhere that I can get the result to be a Count instead of a Sum.
Am I missing something super obvious?  Why would this be coming back as a SUM when I am asking for a Count?

[v-heq-msft]

Hi @brussom ,
Based on your description, first, make sure that in the Manage Aggregates dialog box, the aggregation settings for the NumbersUnder1k table correctly use the COUNT function. Double-check that the Aggregation Type is set to Count instead of Sum, and verify that the property for column N in the NumbersImport_Agg table is set to Do Not Summarize. Sometimes, the default summary settings may override the aggregation function. Then, make sure that the NumbersImport_Agg table is set up correctly as an aggregation table. If there is a difference in the table settings, the mapping may not recognize it correctly.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly