Slow loading visuals / tables

[Dr_BB]

Hi,

Thanks for your help in advance!

Some of my visuals/tables are taking ~1 minute to load, how can I reduce the loading time?

Maximum time is taken by line chart by week.

I have similar structured DAX as follows to switch between weekly, YTD, LTM, followed by respective YoYs. An example is shown below:

Current time period DAX:

sales_current =

VAR yr = SELECTEDVALUE ( 'SALES_DIST_DATE'[YEAR] )

VAR mn =  SELECTEDVALUE ( 'SALES_DIST_DATE'[MONTH] )

VAR wk =  SELECTEDVALUE ( 'SALES_DIST_DATE'[WEEK] )

VAR wknm =  SELECTEDVALUE ( 'SALES_DIST_DATE'[WEEK_CONSECUTIVE] )

var sales_wk = CALCULATE([SALES_SUM], SALES[YEAR] = yr, SALES[WEEK] = wk, SALES[MONTH] = mn )

var sales_ytd = CALCULATE([SALES_SUM], SALES[YEAR] = yr, SALES[WEEK] <= wk, SALES[MONTH] <= mn )

var sales_ltm =  CALCULATE( [SALES_SUM],  SALES[WEEK_CONSECUTIVE] >= wknm - 52,  SALES[WEEK_CONSECUTIVE] <=  wknm  )

return

SWITCH( TRUE(),

SELECTEDVALUE(SALES_TIME_PERIODS[TIME_PERIODS])= "Week", (sales_wk),

SELECTEDVALUE(SALES_TIME_PERIODS[TIME_PERIODS])= "YTD", (sales_ytd),

SELECTEDVALUE(SALES_TIME_PERIODS[TIME_PERIODS])= "LTM", (sales_ltm) )

Previous time period DAX:

sales_ly =

VAR wk =  SELECTEDVALUE ( 'SALES_DIST_DATE'[WEEK] )

VAR wknm =  SELECTEDVALUE ( 'SALES_DIST_DATE'[WEEK_CONSECUTIVE] )

var prev_yr = SELECTEDVALUE('SALES_DIST_DATE'[YEAR])-1

var prv_wknm = CALCULATE(MAX(SALES_DIST_DATE[WEEK_CONSECUTIVE]), SALES_DIST_DATE[WEEK]=wk, SALES_DIST_DATE[YEAR] <= yr-1, SALES_DIST_DATE[MONTH] in ALL(SALES_DIST_DATE[MONTH]) )

var sales_wk_prev = CALCULATE( [SALES_SUM], SALES[WEEK] = wk, SALES[YEAR] = prev_yr )
var sales_ytd_prev = CALCULATE([SALES_SUM], SALES[YEAR] = prev_yr, SALES[WEEK] <= wk, SALES[MONTH] <= mn )
var sales_ltm_prev = CALCULATE( [SALES_SUM] , SALES[WEEK_CONSECUTIVE] >= prv_wknm - 52, SALES[WEEK_CONSECUTIVE] <= prv_wknm )

return
SWITCH( TRUE(),
SELECTEDVALUE(SALES_TIME_PERIODS[TIME_PERIODS])= "Week", FIXED(sales_wk_prev,0,0),
SELECTEDVALUE(SALES_TIME_PERIODS[TIME_PERIODS])= "YTD", FIXED(sales_ytd_prev,0,0),
SELECTEDVALUE(SALES_TIME_PERIODS[TIME_PERIODS])= "LTM", FIXED(sales_ltm_prev,0,0) )

YoY:

yoy = ( sales_current - sales_ly ) / sales_ly

I have multiple KPIs and YoYs (~20 columns) in the matrix which is taking a lot of time to load. Maximum time is taken by a line chart by week which has 2 KPIs. The backend fact data consists of 12million+ rows.
Will adding filter( all( ..... ) .... .... ) in calculate help? or any other step?

Kindly help me to reduce the loading time. Please let me know if more information is needed.

Thanks

[Alex_Sawdo · Accepted solution]

Before digging too far into the DAX, I'd recommend looking up and researching field parameters for Power BI: Use report readers to change visuals (preview) - Power BI | Microsoft Learn. It looks like this could solve many problems and simplify the DAX you've made if you implement some date field parameters (Month, Year, Week, etc.). This way, you don't have to calculate each range individually and can just do a single calculation. 

Also, try to use the FILTER function with AND or OR to help reduce execution time. var sales_ltm_prev can be re-written as:

CALCULATE( 
    [SALES_SUM] , 
    FILTER(
        VALUES(
            SALES[WEEK_CONSECUTIVE]
        ),
        AND(
            SALES[WEEK_CONSECUTIVE] >= prv_wknm - 52, 
            SALES[WEEK_CONSECUTIVE] <= prv_wknm
        )
    )
)

which could help speed up that query significantly.