Forum Discussion
Very Slow Nested Iterator
Your formula will be faster if you create an aggregation table that will squeeze the 'Stock' table so that you don't have to count rows for PartNo in CALCULATE ( COUNTROWS ('Stock') ) but use the number of rows directly. So, you'd have a table aggStock which would be created by appropriately agrouping rows and adding one more column that would tell you the number of rows in the grouping (aggStock[PartCount]). Then the measure would be:
sumx( 'Products', calculate( minx( 'aggStock'[PartCount] ) ) )In aggStock you'd have the same columns as in Stock but it would not be as granular, so that you don't have to count the rows. This is the first speed-up that comes to my mind...
Anonymous AlB Thank you for looking into this and the feedback for more details. I have created a quick sample file with this simplified data model:
* Product contains the product's master data with about 13 million rows in the original model
* PartAgg is an aggregated table. In the real scenario each item on stock has a distinctive ID, the model would grow quickly to several hundred million rows in a dimension without aggregating. Of interest is the partNo and one or two other filterable columns. It contains around 200 rows.
* StorageLocation depicts the physical location of each item. The is just to illustrate that there are several other dimensions on a higher grain related to Stock (~5 million rows)
* Stock is a snapshot once per day with a snapshot date. The fact table contains up to a billion rows
Download here: http://s000.tinyupload.com/?file_id=57163484993199174102
You cannot upload the file here directly. You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers