Memory problem with TopN Measure

[Anonymous]

Hello

I have a table with warehouse inventory information and a table with sales information. I'm trying to get a count of the number of the top 5 products per warehouse. Please see the example below:

Warehouse Inventory:

Sales information (The top 5 products here would be A, B, C, D and H):

Desired end result:

I think this DAX code should work, but I'm working with about 500,000 records and I'm getting an out-of-memory error. Is there a better way to achieve the end result?

var top5 á TOPN(5, SUMMARIZE('Sales', 'Sales'[Product]), [Sales Count]) return CALCULATE(sum('Inventory'[Count of Products]), Filter('Inventory', CONTAINS(top5, 'Sales'[Product], 'Inventory'[Product])))

I apologize if the example doesn't make sense. Let me know if you need more information. Thank you!

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We create three optimized measures and you can try them and verify which one can work.

Measure_1 = 
VAR Top5 =
    CALCULATETABLE (
        DISTINCT ( 'Sales'[Product] ),
        FILTER (
            'Sales',
            RANKX ( GROUPBY ( 'Sales', 'Sales'[Product] ), CALCULATE ( [Sales Count] ) ) <= 5
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

Measure_2 = 
VAR Top5 =
    CALCULATETABLE (
        DISTINCT ( 'Sales'[Product] ),
        TOPN (
            5,
            SUMMARIZE ( 'Sales', 'Sales'[Product] ),
            CALCULATE ( [Sales Count] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

Measure_3 = 
VAR t =
    SUMMARIZE (
        'Sales',
        'Sales'[Product],
        "Temp", CALCULATE ( [Sales Count] )
    )
VAR Top5 =
    SELECTCOLUMNS ( TOPN ( 5, t, [Temp] ), "Produce", [Product] )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

The result like this,

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! It works with the following code:

VAR top5 =
    SELECTCOLUMNS (
        TOPN (
            5,
            SUMMARIZE ( 'Sales', 'Sales'[Product], "Count", [Sales Count] ),
            [Count]
        ),
        "Product", 'Sales'[Product]
    )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN top5
    )