applying a measure filter with date using DAX query

[sanjanapatil]

I am trying to get the customers whose Amount(Measure) is greater than 1000 for the given date period.

But the results I get are less than 1000 even. Does the filter query in DAX doesn't work for Measure with a date?

The DAX query is:

EVALUATE
SUMMARIZECOLUMNS(
Customer[CustomerID],
FILTER( ALL( Customer[CustomerID] ), [Amount] > 1000 ),
KEEPFILTERS( FILTER( ALL( 'Date'[Date] ), 'Date'[Date] >= DATE(2020,1,1) && 'Date'[Date] <= DATE(2020,1,7) )),
"Amount", [Amount])

[sanjanapatil]

Sorry for the delayed response.

I still get the error as "A single value for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

But this seems to work.
 

  VAR WithAmount =
    ADDCOLUMNS(
        VALUES('Customer'[CustomerID])
        ,"Amount",CALCULATE(
                        [Amount]
                        ,'Date'[Date] >= DATE(2020,1,1) && 'Date'[Date] <= DATE(2020,1,7)
                        ,ALL(Date), FILTER('Channel', 'Channel'[Channel] = "Retail")
                     
                    ) 
    )    
RETURN
    FILTER(
        WithAmount
        ,[Amount] >1000
    )

[jennratten]

Do you still want to work out why the other version is not working for you or are you good with the current solution?

[jennratten]

Dax does support what you are trying to do.  I think you will need something like what's listed below.  Note, this is untested.  Pls let me know the outcome.

AmountOverThresholdByDate =
VAR SummarizedTable =
    ADDCOLUMNS (
        SUMMARIZE (
             Customer, 
             Customer[CustomerID],
             'Date'[Date]
        ), "Amount", [Amount]
    )
VAR Result =
    FILTER ( 
        SummarizedTable, 
        [AmountPaid] > 1000 &&
        'Date'[Date] >= DATE(2020,1,1) && 
        'Date'[Date] <= DATE(2020,1,7)
 )
RETURN
    Result

[sanjanapatil]

Hello @jennratten 

Thanks for the reply. 

But I get an error as " A single value for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result".

Is there anything wrong with this?

[jennratten · Accepted solution]

Hello - apologies for the delayed response.  Please try this below.  I removed the addcolumns function and this works for me.

AmountOverThresholdByDate = 
VAR SummarizedTable =
    --ADDCOLUMNS (
        SUMMARIZE (
             -- Add the fact table first so you can also add related dimension columns
             -- and so the result only includes the dimension values that exist.
             -- Add the dimension table first when the result needs to include all
             -- combinations, even those that do not exist in the data.  
             'Benchmark Data Comparison - All Employers Masked',
             'Benchmark Data Comparison - All Employers Masked'[BMClientId],
             'Dates'[Date],
             "Amount", [Median Currency Value]
   --     ), "Amount", [Median Currency Value]
    )
VAR Result =
    FILTER ( 
        SummarizedTable, 
        [Median Currency Value] > 1000 &&
        'Dates'[Date] >= DATE(2022,1,1) && 
        'Dates'[Date] <= DATE(2022,1,7)
 )
RETURN
    Result