Get dynamic rank within groups

[harmeet189]

I have a table containing store name and its capacity to hold goods of each category and another table containing item data like below, please note that capacity is at category level:

I want to create a table containing the possible SKUs combination for all stores within a category based on their capacity, SKU preference is based on code, i.e. SKU code 1 will be preferred than SKU Code 2 if capacity is only 1 for that category.

The report will have filters and the user can remove specific SKUs basis their need, in such a case that SKU will be removed from combination and next SKU will be preferred.

I have created below DAX queries which is giving correct result and working well for dynamic filters:

CATEGORY_CAPACITY =

CALCULATE(

    SELECTEDVALUE(STORE[CAPACITY]),

    STORE[CATEGORY] = SELECTEDVALUE(SKU[CATEGORY])

)

DYNAMIC_SKU_RANK =

RANKX(

    FILTER(

        ALLSELECTED(SKU),

        SKU[CATEGORY] = MAX(SKU[CATEGORY])

    ),

    CALCULATE(MIN(SKU[SKU_ORDERED])),

    ,

    ASC

)

APPLICABLE_SKU =

IF(

    [DYNAMIC_SKU_RANK] <= [SUB_CATEGORY_CAPACITY],

    1,

    0

)

In my output table, I am only showing APPLICABLE_SKU = 1.

When use this approach on my actual data, I am receiving "Query exceeded resources error".

Please note that the dynamic SKU filtering out is crucial, hence ALLSELECTED is used while calculating dynamic RANK. Please help if their is any other approach which can be used or if the existing approach can be optimized for use.

[harmeet189]

I tried the 2nd option, still same error

[Anonymous]

Hi @harmeet189 ,

The "Query exceeded resources error" indicates that the query has consumed more resources than the system can handle, which is typically caused by an excessive volume of data or a high computational load from DAX calculations.

Here are my suggestions to address the issue:

1. Reduce the data volume as much as possible. Remove unnecessary rows or consider splitting the data into smaller, more manageable parts.
2. Minimize the computational workload. For instance, you could perform operations such as creating a Cartesian product between the Store and SKU tables directly at the data source or use Power Query to merge the tables. This would allow you to apply filters in the report after the heavy lifting has already been handled.

I hope this helps, and I’d be happy to assist further if needed.

Best Regards,

Bof