Dax SummarizeColumns with Filter On DateTime Field is not working.

[baijumohan1990]

Hi Experts,

I am trying to get the data from SSAS tabular model with filter applied to the datetime field.  Data stored in that field mm/dd/yyyy format like below.

1/16/2020 10:11:42 AM. 

I wrote a dax query below to retrive the data..

evaluate SUMMARIZECOLUMNS('Campaign Summary Customer pool'[CALLPLACEDTIMEUTC],'Campaign Summary Customer pool'[AGENTID],'Campaign Summary Customer pool'[DIALER_SKILL],
DATESBETWEEN('Campaign Summary Customer pool'[CALLPLACEDUTC],date(2020,01,01),date(2020,09,09)),
"AGENT CALLS",COUNT('Campaign Summary Customer pool'[I3_IDENTITY]))

Got Error "Multiple values supplied for the "CALLPLACEDTIMEUTC"  column" . This i understand because the filter expects a unique value & the field can multiple timestamps for same day. 

So I tried below query,

evaluate SUMMARIZECOLUMNS('Campaign Summary Customer pool'[CALLPLACEDTIMEUTC],'Campaign Summary Customer pool'[AGENTID],'Campaign Summary Customer pool'[DIALER_SKILL],
FILTER(VALUES('Campaign Summary Customer pool'[CALLPLACEDTIMEUTC]),FORMAT('Campaign Summary Customer pool'[CALLPLACEDTIMEUTC],"dd/mm/yyyy") >= format(value(date(2020,09,08)),"dd/mm/yyyy")
&& format('Campaign Summary Customer pool'[CALLPLACEDTIMEUTC],"dd/mm/yyyy")< format(value(date(2020,09,09)),"dd/mm/yyyy")),
"AGENT CALLS",COUNT('Campaign Summary Customer pool'[I3_IDENTITY]))

This runs fine & but giving output with all date ranges available. Date filter is not working.  Any suggesions/Alternative ways to extract date from the datetime field & apply filter on it?

[Anonymous]

Please do yourself a favour and stop using FORMAT. This function is only for occasional formatting of values but NEVER, EVER for doing any kind of comparisons within calculations. If you want to compare dates, then they have to be dates, not strings.