Search code examples
powerbidaxdaxstudio

applying a measure filter with date using DAX query

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])

Any help on this would be grateful. I would like to get the customers who have the Amount(Measure) greater than 1000 for given date period.

Thanks.

Solution 
  • VAR WithAmount =
    ADDCOLUMNS(
        VALUES(Customer[ID])
        ,"myAmount",CALCULATE(
                        [Amount]
                        ,'Date'[Date] >= DATE(2020,1,1) && 'Date'[Date] <= DATE(2020,1,7)
                        ,ALL(Date)                   
                    )                     
    )    
RETURN
    FILTER(
        WithAmount
        ,[myAmount]>1000
    )