Visuals taking long time too load and refresh

souvik900766 · ‎03-14-2021

Hi,
The dataset:-https://www.dropbox.com/scl/fi/tm826pkz2ucu6qfoqhysu/case.xlsx?
dl=0&rlkey=mhxbdwjxuzngha2ptt624q5nk
Power BI dashboard:-https://www.dropbox.com/s/bdtc2t7g440xx7j/try.pbix?dl=0

My Problem :-
My dataset contains around 80k rows and want to show a line chart based on count of Top 99% product and Bottom 1 % product. I have a slicer that has value and volume and some other slicers also. My line chart is taking atleast 25 Mins to load or refresh while switching between slicers.
I want help on how can I reduce the time from 25 mins to 1 mins or less.
All the measures used, is in the dashboard. Kindly see if there is a need to optimize the measures.
I have also tried to use DAX studio but failed to find the issue.
Above is the excel file and power bi link.

jameszhang0805 · ‎03-15-2021

@souvik900766 I have checked your file. Your request is similar to ABC analysis. Because you have too many product IDs(around 25k unique value, your measure calculation takes up a lot of memory, so it will take a long time to refresh.
I have provided 2 kinds of solution :

1st, dynamic measure, I optimized your code. It takes about 32 seconds to query the results, and the FE engine is used, the SE engine has no room for optimization. I think it is not a good solution. Because making users wait so long time after selection is a bad experience.

2nd, Create a calculation table, calculate the results of the cumulated values first. Since the data refresh of the calculation table is done when the data source is refreshed and loaded，so it will save lots of time on the measure calculation. it will also increase the speed of your chart refresh.

Optimize TOP customers.pbix