Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter in Summarize table

Hi
I need to create a summarized table from another table in the same model.
I'm trying to create it using Dax. I've created it with the following expression:
 
Summarized Table = SUMMARIZE('Customers';'Customers'[Date];"Amount";Count('Customers'[UniqueCustomerID]))
 
The problem is that the original table has BLANK/NULL values for the field Date. I don't want to include this null values.
How should I filter this table?
 
Thanks!
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Have try please 🙂

    Summarized Table =
VAR k =
    FILTER ( Customers, 'Customers'[Date] <> BLANK () )
RETURN
    CALCULATETABLE (
        SUMMARIZE (
            'Customers',
            'Customers'[Date],
            "Amount", COUNT ( 'Customers'[UniqueCustomerID] )
        ),
        KEEPFILTERS ( k )
    )

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Have try please 🙂

    Summarized Table =
VAR k =
    FILTER ( Customers, 'Customers'[Date] <> BLANK () )
RETURN
    CALCULATETABLE (
        SUMMARIZE (
            'Customers',
            'Customers'[Date],
            "Amount", COUNT ( 'Customers'[UniqueCustomerID] )
        ),
        KEEPFILTERS ( k )
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    You can use CALCULATABLE() and it works like a CALCULATE() in measures.

     

    Would look like: Summarized Table = CALCULATETABLE(SUMMARIZE('Customers';'Customers'[Date];"Amount";Count('Customers'[UniqueCustomerID]));Customers'[Date]<>"")

     

    Let me know if it worked, you might have to adjust the comparison symbols (I'm not sure which compares with date right now, if it works mark as solution.

     

    BR,

    DR