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?

[v-hjannapu · Accepted solution]

Hi @derekli1700 ,

Thank you  for reaching out to the Microsoft fabric community forum.

Using DAX Studio, Yugandhar identified that the dynamic customer ranking per site was significantly increasing query load, especially given the large volume of data and multiple Excel sources.
A few things you might want to try: If your customer-site combinations don’t change too often, calculating the ranks during data load can really help reduce the pressure on your visuals. Creating a separate summary table (like customer-site-month) can make your visuals much faster and easier to manage. If you can reduce how many times you're calculating the same measure inside your ranking logic, that can go a long way. And while the Top N filter pane doesn’t work per site, it might still be useful in other parts of your report.

Best Regards,
Harshitha.

[v-hjannapu]

Hi @derekli1700 ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[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.