Sales matrix, product hierarchy on rows, months across columns, four measures. Two million rows in the fact table, which I thought was nothing. It takes 35-45 seconds every time a slicer changes. Other visuals on the same page are under a second. No calculated columns and the file is 180MB.
A matrix that renders tens of thousands of cells is slow no matter how good your model is, because each cell is its own filter context evaluation. Start it collapsed to the top level, turn off show items with no data, and cap the columns to 12 months rather than three years. Users almost never look at all of it and they can expand what they want.
This is the underrated one. I have had reports go from 20 seconds to instant purely by collapsing the default state of one hierarchy.
180MB for 2 million rows suggests something high cardinality is riding along. Usually a transaction ID or a full datetime stamp. Split datetime into a date column and a time column, drop any ID you do not actually join or count on, and watch the model shrink. Narrower columns compress better and scan faster.
Do you have a measure calling DISTINCTCOUNT on a high cardinality column? Those are quietly expensive and people sprinkle them everywhere because they read as harmless.
@tabula_rasa_dev · last mo. · 3 replies
Row count is almost never the problem at that size. Two things to check before anything else.
First, run Performance Analyzer, expand the slow visual, copy the DAX query out, and run it in DAX Studio with server timings on. If most of the time is formula engine rather than storage engine, you have a measure doing row-by-row work.
Second, look for
FILTER(BigTable, ...)inside your CALCULATE calls. That materialises the whole table. Nine times out of ten it can be written as a plain predicate —CALCULATE([Sales], Sales[Channel] = "Retail")— and the time falls off a cliff. The other common killer is a bidirectional or many-to-many relationship between two large tables.Reply
Report
@iron_camber · last mo.
Formula engine was 91% of the time. Two of the four measures had FILTER over the fact table because I copied the pattern from a blog post years ago. Rewrote both, now 4 seconds.
Reply
Report
@csv_apologist · last mo.
Four seconds is still slow for that shape. Worth carrying on down the list — the matrix cell count is probably doing the rest.
Reply
Report