Visual loading time is taking forever

[qLollo]

Hi all,

I've been having issues with my report, in particular, with 2 visuals.

My dataset is pretty big, i have 40+ tables, each one has god knows how many rows, but that's not the problem, because yeah maybe the visuals may take a few seconds more than normal, but all my 9 pages of my report work fine, all the filters and stuff.
The slow loading comes when i use the measure below in a Matrix visual:

OUT CPA = 

VAR OUT = SUMMARIZE('CC Call','CC Call'[AGENT_ID], 'Last Sale Aging History'[Aging Cluster English Desc],'Customer History - Last Interaction'[Interaction Last 12 Months Flag], 'Customer Lifecycle History'[Customer Type Group], 

"GroupBy", SWITCH(TRUE(),

SELECTEDVALUE('Last Sale Aging History'[Aging Cluster English Desc]) = "USER - Hot" && SELECTEDVALUE('Customer History - Last Interaction'[Interaction Last 12 Months Flag]) = 1, (1.99 * [OUT Useful Contact]) + (2.67 * [OUT Call Appointment Booked]),

SELECTEDVALUE('Last Sale Aging History'[Aging Cluster English Desc]) = "USER - Hot" && SELECTEDVALUE('Customer History - Last Interaction'[Interaction Last 12 Months Flag]) = 0, (1.99 * [OUT Useful Contact]) + (2.94 * [OUT Call Appointment Booked]),

SELECTEDVALUE('Last Sale Aging History'[Aging Cluster English Desc]) = "USER - Churn Risk" && SELECTEDVALUE('Customer History - Last Interaction'[Interaction Last 12 Months Flag]) = 1, (1.99 * [OUT Useful Contact]) + (3.02 * [OUT Call Appointment Booked]),

SELECTEDVALUE('Last Sale Aging History'[Aging Cluster English Desc]) = "USER - Churn Risk" && SELECTEDVALUE('Customer History - Last Interaction'[Interaction Last 12 Months Flag]) = 0, (1.99 * [OUT Useful Contact]) + (4 * [OUT Call Appointment Booked]),

SELECTEDVALUE('Last Sale Aging History'[Aging Cluster English Desc]) = "USER - Cold" && SELECTEDVALUE('Customer Lifecycle History'[Customer Type Group]) = "CUSTOMER", (1.99 * [OUT Useful Contact]) + (2.09*[OUT Call Appointment Booked]),

SELECTEDVALUE('Customer Lifecycle History'[Customer Type Group]) = "LEAD" || SELECTEDVALUE('Customer Lifecycle History'[Customer Type Group]) = "PROSPECT", (1.99 * [OUT Useful Contact]) + (2.09*[OUT Call Appointment Booked]))) 

VAR NUM = SUMX(OUT, [GroupBy]) 

VAR DEN = [OUT Call Appointment Booked]

return divide(NUM, DEN)

Long story short, i need to make a calculation based on a big IF, with each condition has a calculation.

This big switch is probably the problem of the loading time, but i wouldn't know how to optimize this.

Thanks

[v-henryk-mstf]

Hi @qLollo ,

After reviewing the formulas you provided, there does not seem to be a complex calculation logic. I think it is indeed a problem caused by loading a data set that is too large.
What is your data source? Is it possible to do some filtering of the data before importing (filtering out the parts that are not relevant to the calculation), which would optimize the speed of loading.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[qLollo]

Hi @v-henryk-mstf ,

I've been able to reduce the loading time from 10 mins to 2-3mins just by changing the formula, and making it as the IF does firstly the condition with the most amount of rows, so it will not waste time checking a lot of rows, i think it's not possible to reduce the time more than this, since i can't filter the data more or reduce the data set, so i consider this solved.

[ribisht17 · Accepted solution]

Please check this blog 

1.  Power BI Performance Analyzer - Power BI Docs

2.  Performance Tuning DAX - Part 1 - Microsoft Power BI Community

Regards,

Ritesh

[qLollo]

I have already tried using it, also tried to clean up the code, but haven't gone too far..