Forum Discussion
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])
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
5 Replies
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 ResultHello 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?
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
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 )Do you still want to work out why the other version is not working for you or are you good with the current solution?
