Forum Discussion
Solved
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 Me...
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?