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.
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 = SUM(vwCallScriptFlow[PageEndTime])-SUM(vwCallScriptFlow[PageStartTime])
Durationsumx = SUMX(vwCallScriptFlow,HOUR(vwCallScriptFlow[Duration])*3600+MINUTE(vwCallScriptFlow[Duration])*60+SECOND(vwCallScriptFlow[Duration]))
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.
Solved! Go to Solution.
Hi @BenBrubacher ,
The reason for this problem could be the performance of calculated columns.
You can try to use the performance analyzer or DAX studio to see DAX performance.
https://www.sqlbi.com/articles/capturing-power-bi-queries-using-dax-studio/
Improve Power BI Performance by Optimizing DAX
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BenBrubacher ,
The reason for this problem could be the performance of calculated columns.
You can try to use the performance analyzer or DAX studio to see DAX performance.
https://www.sqlbi.com/articles/capturing-power-bi-queries-using-dax-studio/
Improve Power BI Performance by Optimizing DAX
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |