Hello,
Is there a more efficient way I can design my calculated fields as I keep getting an error that I am out of memory. There’s under 1 million rows to be returned. I can get 12,000 rows returned if tell BI to return the bottom x rows, that’s with 8GB RAM. My colleague can return the bottom 18,000 rows with 16GB RAM.
I have a transactional database which contains the time an agent spends on each page for each call. I have left 6 columns with data out of this sample.
| AgentName | CSReference | Durationsumx | Duration | PageStartTime | PageEndTime | Call_end_time | Instances | OutboundHistoryID | StepDescription |
| Justin | Call 2 | 0 | 1899-12-30 00:00:00 | 2020-10-02 7:54:52 | 2020-10-02 7:54:52 | 2020-10-02 8:10:09 | 1 | 260052 | 00 Post Call Action Check |
| Justin | Call 2 | 20 | 1899-12-30 00:00:20 | 2020-10-02 7:54:52 | 2020-10-02 7:55:13 | 2020-10-02 8:10:09 | 1 | 260052 | 01 Start |
| Justin | Call 2 | 866 | 1899-12-30 00:14:26 | 2020-10-02 7:55:13 | 2020-10-02 8:09:40 | 2020-10-02 8:10:09 | 1 | 260052 | 11 Personal Details |
| Justin | Call 2 | 8 | 1899-12-30 00:00:08 | 2020-10-02 8:09:40 | 2020-10-02 8:09:49 | 2020-10-02 8:10:09 | 1 | 260052 | 12.1 Auto Claim Centre |
| Justin | Call 2 | 1 | 1899-12-30 00:00:01 | 2020-10-02 8:09:49 | 2020-10-02 8:09:51 | 2020-10-02 8:10:09 | 1 | 260052 | 79 Emergency Determination |
| Justin | Call 2 | 9 | 1899-12-30 00:00:09 | 2020-10-02 8:09:51 | 2020-10-02 8:10:00 | 2020-10-02 8:10:09 | 1 | 260052 | 80 Non Emergency Close |
| Justin | Call 2 | 5 | 1899-12-30 00:00:05 | 2020-10-02 8:10:00 | 2020-10-02 8:10:06 | 2020-10-02 8:10:09 | 1 | 260052 | 95 Wrap |
| Justin | Call 2 | 0 | 1899-12-30 00:00:00 | 2020-10-02 8:10:06 | 2020-10-02 8:10:07 | 2020-10-02 8:10:09 | 1 | 260052 | 98 Set Outcomes/Rescheduling |
| Justin | Call 2 | 0 | 1899-12-30 00:00:00 | 2020-10-02 8:10:07 | 2020-10-02 8:10:07 | 2020-10-02 8:10:09 | 1 | 260052 | 98.5 Update Campaign Search |
| Justin | Call 2 | 1 | 1899-12-30 00:00:01 | 2020-10-02 8:10:07 | 2020-10-02 8:10:09 | 2020-10-02 8:10:09 | 1 | 260052 | 98.6 RingCentral Disposition |
| Justin | Call 2 | 0 | 1899-12-30 00:00:00 | 2020-10-02 8:10:09 | 2020-10-02 8:10:09 | 2020-10-02 8:10:09 | 1 | 260052 | 99 End |
In total, I have 4 measures and 1 calculated column.
- Duration
- Durationsumx
- Instances
- Avg Duration
- PageEndTime – calculated column
Duration = SUM(vwCallScriptFlow[PageEndTime])-SUM(vwCallScriptFlow[PageStartTime])
Durationsumx = SUMX(vwCallScriptFlow,HOUR(vwCallScriptFlow[Duration])*3600+MINUTE(vwCallScriptFlow[Duration])*60+SECOND(vwCallScriptFlow[Duration]))
- This is to convert the above measure from a date/time format into seconds
Instances = [Durationsumx]/ [Instances]
PageEndTime =
var previousRow=
TOPN(
1,
FILTER(vwCallScriptFlow, vwCallScriptFlow[PageStartTime] > EARLIER (vwCallScriptFlow[PageStartTime])
&& vwCallScriptFlow[OutboundHistoryID] = EARLIER(vwCallScriptFlow[OutboundHistoryID])
),
vwCallScriptFlow[PageStartTime],ASC
)
var previousvalue= MinX( previousrow, vwCallScriptFlow[PageStartTime])
var fin = if(ISBLANK(previousvalue),vwCallScriptFlow[Call_end_time],previousvalue)
return fin
For the calculated column with page end time, I don’t actually have that field in my database, I only have the transaction start time (PageStartTime), so I need to look up the next row to find the end time for each page, and for the final page I use the end_time which is the time the call ultimately ended.
I am very new to BI and not familiar with how to write efficiently, any help appreciated.
