Issue with Summarizing Table

[purple_SP]

Hi all, I'm trying to create an aggregated version of a fact table and am running into some issues with the summarization.

My Fact table is in this format:

query_id	month	source	product	location	indicator	category
123	01/01/2024	Phone	bike	Texas	0	Change of Mind
547	01/01/2024	In Person	bike	California	1	Faulty
123	01/01/2024	Phone	car	Texas	0	Change of Mind

I am trying to summarize this into a simple aggregated table with the count of distinct ids like so:

month	source	product	location	indicator	category	total_queries
01/01/2024	Phone	bike	Texas	0	Change of Mind	4
01/01/2024	In Person	bike	California	1	Faulty	9
01/01/2024	Phone	car	Texas	0	Change of Mind	45

I am using this DAX to generate the table:

SUMMARIZED_QUERIES = SUMMARIZE(FACT_TABLE,FACT_TABLE[category],FACT_TABLE[indicator],FACT_TABLE[location],FACT_TABLE[product],FACT_TABLE[source],FACT_TABLE[month],"total_queries",DISTINCTCOUNT(FACT_TABLE[query_id]))

However when I compare the sum of the total_queries column (by filtering one of the categories for example) with the distinct count of query ids for the same category filter in the original table, the numbers are not matching.

I was wondering why this might be the case?

[dharmendars007 · Accepted solution]

Hello @purple_SP , 

Please try adding the "All" or "AllSelected" function within your summarized formula so that you can ignore specific filters if there is any..

SUMMARIZECOLUMNS(
FACT_TABLE[month],
FACT_TABLE[source],
FACT_TABLE[product],
FACT_TABLE[location],
FACT_TABLE[category],
FACT_TABLE[indicator],"total_queries", DISTINCTCOUNT(ALL(FACT_TABLE[query_id])))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN