Power BI Performance

[AlexNYExcel]

Hello Everybody,
I am seeking for answer about Performance of Power BI.
Originally I had 2GB Access database, converted into Power BI, with some other small tables it give me 130MB PBIX.

After creating the model I realized it is too slow, does not pass "3 sec" test.
I did following steps:
1. Get rid of all unnecessary columns in data table
2. Convert Snowflake to Star-schema with "RELATED"
3. Rewritten all Formulas that contain FILTER - to avoid it completely (e.g. in CALCULATE), or used reference to Column instead of Tables with VALUES
4. Rewritten all SUMX to CALCULATE(SUM...
5. Get rid of all Calculated Columns that not used in Ranking or as Connectors/Slicers in Model
6. Deleted all unused anymore measures
7. Simplified SuperFormulas with 3 and more iterations to single

Now my files has:
Size - 26MB (pretty good compression from 130MB)
Data Table 2.5M rows x 12 columns
Big Lookup table 31k rows x 9 columns (with calculated columns - 32)
Tables used as Related for BIG Lookup Table 50K row x 13 columns
9 small Tables - each no more than 300 rows x 5 columns

But it still does not pass 3 sec.test.

Let me know, maybe I missed something in rebuilding model?
Or just POWER BI so slow???

 

[Matt Allington]

Tia is a very complex topic and it is hard to say without seeing the workbook. Do you have many slicers? These can be a big drain on performance. I suggest take a copy of the workbook and the. Delete everything except 1 single pivot with just 1 or 2 slicers. If that fixes it, then go back to you main copy and turn off slicer visualisations in the slicer settings dialog.

 

[AlexNYExcel]

Matt,
there is no configuration of slicers in Power BI, and as it turned out it does not affect performance at all even if slicers has ~30K values, or no slicer at all.
So far biggest speed contributor - Calculated columns instead of Measure for some very complex formulas.

 

[Matt Allington]

Ah, sorry. Microsoft's confusing language. I think of Power BI as Power Pivot - my bad. I would really be surprised if Calc columns are the issue. It shouldn't really matter how complex they are as they are not calculated at runtime. Less columns in a data table will always help, but given this is Power BI in the cloud, I can't imagine that 2.5 m rows with 12 columns was the cause.

The he other things I would suggest next
1. Convert your snowflake schema to a Star schema in the database via a Query and import directly as a star schema. Delete the snowflakes (and relationships and related formulas) all together. This is always preferred if possible
2. when you say you have rewritten all formulas with filter, how did you do that? I normally would not use filter for any formula if it could be rewritten another way. So I wonder if the new formulas are also iteratiors or maybe inefficient.
3. Download and install DAX Studio and use the profiler to see what is going on. There is a cool feature that shows if the storage engine or the formula engine is being used. I have to assume your formulas are using the formula engine, and this can be much slower.