Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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])
Solved! Go to 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
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
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?
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
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
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
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?
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
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User