cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 moreit 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Summarize filtered results

Hi @Deamo,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
3 REPLIES 3
Community Support Team

## Re: Summarize filtered results

Hi @Deamo,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Frequent Visitor

## Re: Summarize filtered results

Thanks @v-shex-msft

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

Community Support Team

## Re: Summarize filtered results

Hi @Deamo,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |