More columns or more rows are better for performance?

[Quiny_Harl]

Hi Pbi folks,

I'm optimizing a report which includes complex measures, similar to the ones in this dax pattern.

My measures uses SWITCH to switch between different columns in the Fact table depending on a selection from a slicer. I'm considering to unpivot the columns in order to avoid using SWITCH. This, of course, will increase the number of rows. So I'm wondering which would be better in terms of performance/loading time:
Scenario 1:
A fact table with 38 columns and 3 million rows.
Scenario 2:
A fact table with 18 columns and 72 million rows?

[Quiny_Harl]

@v-jianpeng-msft, thank you for your explanation!
Can you please tell me what does "exclusive duration" mean?

[v-jianpeng-msft · Accepted solution]

Hi, @Quiny_Harl 

Selecting A fact table with 38 columns and 3 million rows in your model will be even better.
To improve performance, we often need to reduce redundant columns and rows.
If adding a few columns can effectively reduce the number of rows, then it will be very helpful for performance. In my 38x50 and 18x100 model tests, when Power query reads the data file, the 38x50 time is significantly better than the 18x100 as shown in the following image:

Let's take a look at how they perform in visuals:

This tells us that if a visual has too many columns, it will lead to slow performance. With 38 columns, don't put all the columns in the same visual at once.

Here are some optimization rules that will help you optimize your model for a good user experience:

Best practice rules to improve your model’s performance | Microsoft Power BI Blog | Microsoft ...

Best Regards

Jianpeng Li

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