cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Inefficient Calculated Fields? Out of Memory

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.

 

AgentNameCSReferenceDurationsumxDurationPageStartTimePageEndTimeCall_end_timeInstancesOutboundHistoryIDStepDescription
JustinCall 201899-12-30 00:00:002020-10-02 7:54:522020-10-02 7:54:522020-10-02 8:10:09126005200 Post Call Action Check
JustinCall 2201899-12-30 00:00:202020-10-02 7:54:522020-10-02 7:55:132020-10-02 8:10:09126005201 Start
JustinCall 28661899-12-30 00:14:262020-10-02 7:55:132020-10-02 8:09:402020-10-02 8:10:09126005211 Personal Details
JustinCall 281899-12-30 00:00:082020-10-02 8:09:402020-10-02 8:09:492020-10-02 8:10:09126005212.1 Auto Claim Centre
JustinCall 211899-12-30 00:00:012020-10-02 8:09:492020-10-02 8:09:512020-10-02 8:10:09126005279 Emergency Determination
JustinCall 291899-12-30 00:00:092020-10-02 8:09:512020-10-02 8:10:002020-10-02 8:10:09126005280 Non Emergency Close
JustinCall 251899-12-30 00:00:052020-10-02 8:10:002020-10-02 8:10:062020-10-02 8:10:09126005295 Wrap
JustinCall 201899-12-30 00:00:002020-10-02 8:10:062020-10-02 8:10:072020-10-02 8:10:09126005298 Set Outcomes/Rescheduling
JustinCall 201899-12-30 00:00:002020-10-02 8:10:072020-10-02 8:10:072020-10-02 8:10:09126005298.5 Update Campaign Search
JustinCall 211899-12-30 00:00:012020-10-02 8:10:072020-10-02 8:10:092020-10-02 8:10:09126005298.6  RingCentral Disposition
JustinCall 201899-12-30 00:00:002020-10-02 8:10:092020-10-02 8:10:092020-10-02 8:10:09126005299 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.

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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 

https://community.powerbi.com/t5/Community-Blog/CALCUHATE-Why-I-Don-t-Use-DAX-s-CALCULATE-Function/b... 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Microsoft
Microsoft

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 

https://community.powerbi.com/t5/Community-Blog/CALCUHATE-Why-I-Don-t-Use-DAX-s-CALCULATE-Function/b... 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors