Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
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:
Calculated counts within selected date range
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |