Forum Discussion
Summarize filtered results
I have a table of customers and the number of times they have called each day, as below:
Date - CustomerPh - TimesCalled
I want to get to a level of displaying how many consumers have called x number of times within the date range selected on the report. The grouping will be:
1 call - [Customer Count]
2 calls - [Customer Count]
...
7+ calls - [Customer Count]
Ive tried to summarize with filter to get a total count of customers calling within the date range - this is just returning totals.
In the dax below, Max & MinDateRng are measures that return the start and end of the selected date range
CustomerCalls = SUMMARIZE(
filter(RepeatCallers
,RepeatCallers[CallDate]>=[MinDateRng] && RepeatCallers[CallDate]<=[MaxDateRng])
,RepeatCallers[CallerNumber]
,"CustCount"
,sum(RepeatCallers[CountOfCalls]
)
)
Edit: after playing around some more, it looks like Summarize is evaluated prior to the report filter being applied, and therefore the date isn't picked up in the measure so i get totals instead.. is that right?? if so is there any way around that?
Hi Anonymous,
You can refer to below sample to get the total count of specify range.
Original Table:
Create a CALENDAR table with original date:
Table = CALENDAR(MIN(Sheet1[Date]),MAX(Sheet1[Date]))
Add two measures to get the date range.
MinDate = FIRSTDATE(ALLSELECTED('Table'[Date]))
MaxDate = LASTDATE(ALLSELECTED('Table'[Date]))
Write a measure to get the total count bease on select date range.
CountOfSpecifyRange =
var currNumber=LASTNONBLANK(Sheet1[CallNumber],[CallNumber])
return
SUMX(FILTER(ALL(Sheet1),Sheet1[CallNumber]=currNumber&&AND([Date]>=[MinDate],[Date]<=[MaxDate])),[CountofCall])Create visuals:
Slicer.
Matrix.
In addition, you can direct use the original table to get the result:
Regards,
Xiaoxin Sheng
3 Replies
Hi Anonymous,
You can try to use below formula if it suitable for your requirement:
CustomerCalls var temp= filter(All(RepeatCallers),RepeatCallers[CallDate]>=[MinDateRng] && RepeatCallers[CallDate]<=[MaxDateRng]) return SUMMARIZE(temp,[CallerNumber],"CustCount",SUMX(FILTER(temp,[CallerNumber]=EARLIER([CallerNumber])),[CountOfCalls]))
If above is not help, please share some sample data.
Regards,
Xiaoxin Sheng
Thanks Anonymous
That doesn't work either - still returning all rows from my Repeat Callers table, regardless of the date range selected on the report itself.
The screenshots below are from a spreadsheet i've created that shows what i'm trying to do..
Raw Data:
RawData
Calculated counts within selected date range
Hi Anonymous,
You can refer to below sample to get the total count of specify range.
Original Table:
Create a CALENDAR table with original date:
Table = CALENDAR(MIN(Sheet1[Date]),MAX(Sheet1[Date]))
Add two measures to get the date range.
MinDate = FIRSTDATE(ALLSELECTED('Table'[Date]))
MaxDate = LASTDATE(ALLSELECTED('Table'[Date]))
Write a measure to get the total count bease on select date range.
CountOfSpecifyRange =
var currNumber=LASTNONBLANK(Sheet1[CallNumber],[CallNumber])
return
SUMX(FILTER(ALL(Sheet1),Sheet1[CallNumber]=currNumber&&AND([Date]>=[MinDate],[Date]<=[MaxDate])),[CountofCall])Create visuals:
Slicer.
Matrix.
In addition, you can direct use the original table to get the result:
Regards,
Xiaoxin Sheng