Query keeps exceeding available resources when published - How can i optimise?

[derekli1700]

Hi all,
Currently have a few million rows of data going from 2024 - current showing customer sales (Sum Column 1) inside stores/sites as well as other metrics. My current matrix table shows sites as the first row and then the second row are the top 5 customers of those sites/stores (based off Sum Column 1 and date slicer picked). I have turned query limits off in my current file but when published, the file keeps exceeding query resources and not quite sure how to optimise it better as it's feeding on 4 appended excel sheets.

I rank my customers using this measure:

Customer Rank = 
VAR SelectedDates =
    CALCULATETABLE(
        VALUES('Date'[Date]),
        ALLSELECTED('Date')
    )

VAR CurrentCustomer = SELECTEDVALUE('Customers'[Customer])
VAR CurrentSite = SELECTEDVALUE('Sites'[Site]) // or whatever your site field is

VAR CustomerSales =
    CALCULATE(
        [Total of Sum Column 1],
        KEEPFILTERS(SelectedDates),
        ALL('Date'), // Freeze date context
        'Sites'[Site] = CurrentSite
    )

RETURN
IF(
    ISINSCOPE('Customers'[Customer]),
    IF(NOT ISBLANK(CustomerSales) && CustomerSales > 0,
        RANKX(
            FILTER(
                ALL('Customers'[Customer]),
                CALCULATE(
                    [Total of Sum Column 1],
                    KEEPFILTERS(SelectedDates),
                    ALL('Date'),
                    'Sites'[Site] = CurrentSite
                ) > 0
            ),
            CALCULATE(
                [Total of Sum Column 1],
                KEEPFILTERS(SelectedDates),
                ALL('Date'),
                'Sites'[Site] = CurrentSite
            ),
            ,
            DESC,
            DENSE
        )
    )
)

and have these example measures below as the matrix table's values (Sum of Column 1-2 and Average of Column 1-6 to represent each of the table's column in the picture you see above for each store's top 5 customers) :

MEASURE-Sum Column 1 = VAR CurrentRank = [Customer Rank]
VAR IsTop5Customer = IF(NOT ISBLANK(CurrentRank), CurrentRank <= 5, FALSE) 

RETURN
IF(
    ISINSCOPE('Customers'[Customer]), 
    IF(
        IsTop5Customer, 
        [Total of Sum Column 1],  
        BLANK()         
    ),
    
    CALCULATE(
        [Total of Sum Column 1],
        REMOVEFILTERS('Customers'[Customer]) 
    )
)

MEASURE-Average Column 1 = 
VAR CurrentCustomerRankByVolume = [Customer Rank]
VAR IsTop5CustomerByVolume = IF(NOT ISBLANK(CurrentCustomerRankByVolume), CurrentCustomerRankByVolume <= 5, FALSE)

RETURN
IF(
    ISINSCOPE('Customers'[Customer]),
    IF(
        IsTop5CustomerByVolume,
        [Average of Average Column 1],
        BLANK()
    ),
    CALCULATE(
        [Average of Average Column 1],
        REMOVEFILTERS('Customers')
    )
)

I also made a slicer to show the top/bottom 20 sites based off "Sum Column 1"'s value through this table:

Slicer Options = 
DATATABLE (
    "Option", STRING,
    {
        { "Top 20" },
        { "Bottom 20" }
    }
)

_StoreRankBottom = 
RANKX(
    ALLSELECTED('Sites'[Site]),
    CALCULATE([StoreOverallSum1Column1]),
    , 
    ASC, 
    DENSE 
)

_StoreRankTop = 
RANKX(
    ALLSELECTED('Sites'[Site]),
    CALCULATE([StoreOverallSum1Column1]),
    , 
    DESC, 
    DENSE 
)

StoreOverallSum1Column1 = 
VAR MinDateSelected = MINX(ALLSELECTED('Date'), 'Date'[Date])
VAR MaxDateSelected = MAXX(ALLSELECTED('Date'), 'Date'[Date])
VAR CalculatedVolume =
    IF(
        NOT ISBLANK(MinDateSelected) && NOT ISBLANK(MaxDateSelected),
        CALCULATE(
            [Total of Sum Column 1],
            REMOVEFILTERS('Customers'[Customer]),
            REMOVEFILTERS('Date'),            
            KEEPFILTERS(                
                FILTER(
                    ALL('Date'),
                    'Date'[Date] >= MinDateSelected && 'Date'[Date] <= MaxDateSelected
                )
            )
        ),
        CALCULATE(
            [Total of Sum Column 1],
            REMOVEFILTERS('Customers'[Customer]),
            REMOVEFILTERS('Date')
        )
    )
RETURN
    IF(ISBLANK(CalculatedVolume), 0, CalculatedVolume)

Rank Measure = 
VAR SelectedOption = SELECTEDVALUE('Slicer Options'[Option])
VAR N_Count = 20 
RETURN
IF(
    NOT ISINSCOPE('Sites'[Site]), 
    1, 
    IF(
        ISBLANK(SelectedOption), 
        1, 
        VAR CurrentSiteRankTop = [_StoreRankTop]
        VAR CurrentSiteRankBottom = [_StoreRankBottom]
        RETURN
            SWITCH(
                TRUE(),
                SelectedOption = "Top 20" && NOT ISBLANK(CurrentSiteRankTop) && CurrentSiteRankTop <= N_Count, 1,
                SelectedOption = "Bottom 20" && NOT ISBLANK(CurrentSiteRankBottom) && CurrentSiteRankBottom <= N_Count, 1,
                0 
            )
    )
)

The above measure is set to =1 in the table filter visual.

File attached

Datasheet attached

I am assuming i keep exceeded query limits due to the customer rank measure that is creating a long time to load. Is what im filtering for (top 5 customers and top/bottom store slicer) simply possible given the vast amount of data?

[lbendlin]

Use DAX Studio to examine the query plan and server timings.  Rearrange your query to reduce cardinality earlier.

[ray_aramburo]

Hi @derekli1700 both assumptions are correct. Is there a way you can reduce your data source volume? Other thing you can try is using the Top N filters from the Filters Pane. Not as great as measures but usually run faster than them.

[derekli1700]

Hey - for top n filter pane regarding customers, it only shows the rows for the top n customers for every site instead of that specific site. 
i.e if customer 1,4,6,3,7 were the customers of the highest value in the whole dataset, then the top n filter pane would only show rows for customer 1,4,6,3,7 under each site purely - meaning some rows under stores would be blank as they might not have interacted with those selected customers.