Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sanjanapatil
Frequent Visitor

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

 

 

 

1 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

jennratten_0-1658834138351.png

 

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

View solution in original post

5 REPLIES 5
sanjanapatil
Frequent Visitor

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

jennratten
Super User
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

jennratten_0-1658834138351.png

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.