CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
bmsqldev
bmsqldev

Reputation: 2735

Dax SummarizeColumns with Filter On DateTime Field is not working

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

Answers (1)

msta42a
msta42a

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

Related Questions