Forum Discussion

purple_SP's avatar
purple_SP
Helper I
1 year ago
Solved

Issue with Summarizing Table

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_idmonthsourceproductlocationindicatorcategory
12301/01/2024 PhonebikeTexas 0Change of Mind
54701/01/2024 In PersonbikeCalifornia 1Faulty
12301/01/2024 PhonecarTexas 0Change of Mind

 

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

 

monthsourceproductlocationindicatorcategorytotal_queries
01/01/2024 Phone bikeTexas 0Change of Mind 4
01/01/2024 In Person bikeCalifornia 1Faulty 9
01/01/2024 Phone carTexas 0Change 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's avatar
    dharmendars007
    1 year ago

    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 

1 Reply

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    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