Forum Discussion

sanjanapatil's avatar
sanjanapatil
Frequent Visitor
4 years ago
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...
Need Help
  • jennratten's avatar
    jennratten
    4 years ago

    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's avatar
jennratten
Super User
4 years ago

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

 

sanjanapatil's avatar
sanjanapatil
Frequent Visitor
4 years ago

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?

  • jennratten's avatar
    jennratten
    Super User
    4 years ago

    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