Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone!
I want to be able to calculate the maximum number of calls that happen on a given day within a date range. I am using a simple
CALCULATE(COUNT(DNIS))
To obtain the number of calls that come from a specific number on a given day.
The table that I want to add the Max Quantity of calls looks like this:
Campaign | Mail Drop Date | Quantity |
August | 2020-08-28 | |
October | 2020-10-05 | |
November | 2020-11-15 |
The table that I am getting my call volume looks like this:
Date | phonenumber | quality call |
2020-09-09 | 8001111111 | 1 |
2020-09-09 | 8001111111 | 0 |
2020-09-10 | 8001111111 | 1 |
2020-10-06 | 8001111112 | 0 |
2020-10-06 | 8001111112 | 1 |
2020-10-06 | 8001111111 | 0 |
2020-11-18 | 8001111111 | 1 |
To enhance the challenge here, the days to take into account should be contraint by the mail drop date.. How can I create a function or write code to make it happen? We can add an additonal column to the first table with a Mail Drop End Date base on the date of the row below from the Mail Drop Date column.
I really appreciate any insights!
Solved! Go to Solution.
Hi @albatross19 ,
Not sure whether I have caught your meaning,see the method below:
Create 2 columns as below:
Related date = CALCULATE(MIN('Table (2)'[Mail Drop Date]),FILTER('Table (2)','Table (2)'[Mail Drop Date]>EARLIER('Table'[Date])))
Date Quantity = CALCULATE(COUNT('Table'[quality call]),FILTER('Table','Table'[Related date]=EARLIER('Table (2)'[Mail Drop Date])))+0
And you will see:
For the related .pbix file,pls see attached.
Hi @albatross19 ,
Not sure whether I have caught your meaning,see the method below:
Create 2 columns as below:
Related date = CALCULATE(MIN('Table (2)'[Mail Drop Date]),FILTER('Table (2)','Table (2)'[Mail Drop Date]>EARLIER('Table'[Date])))
Date Quantity = CALCULATE(COUNT('Table'[quality call]),FILTER('Table','Table'[Related date]=EARLIER('Table (2)'[Mail Drop Date])))+0
And you will see:
For the related .pbix file,pls see attached.
@albatross19 Sorry, having trouble following this. You do not want to create a column from a measure typically since measures are dynamic and calculated columns are not. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Otherwise, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.