Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]))
Solved! Go to 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
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |