Forum Discussion
Memory problem with TopN Measure
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!
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.
2 Replies
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.