Reputation: 2735
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?
Upvotes: 0
Views: 1123
Reputation: 3741
Please check if you get properly formated date from your model
at first:
evaluate row("x",format(value(date(2020,09,20)),"dd/mm/yyyy"),"Y",format(DATEVALUE("1/16/2020 10:11:42 AM"),"dd/mm/yyyy"))
Second: evaulate summarizecolumns( 'Campaign Summary Customer pool'[CALLPLACEDTIMEUTC], FORMAT('Campaign Summary Customer pool'[CALLPLACEDTIMEUTC],"dd/mm/yyyy") )
Maybe this will show you where the problem is.
Upvotes: 1