Forum Discussion
64-bit Excel Power Pivot stuck on "reading data" for a small one-to-many relationship
I see, thank you for the feedback. PivotTable, if no aggregation (other words nothing in Values) returns Full Join of two tables which are in relationship. In your case that's 63,000 x 6,000 = 378 million of values.
That takes time, especially taking into account that, most probably, for major part of the data is used memory cache on hard drive.
SergeiBaklan thanks for your reply.
I found the solution: I needed to put the account number in the Values part of the PivotTable Fields pane of Excel (and change to show "Count of AccountNumber")!!! This way, the PivotTable is nice and quick.
As soon as I remove the "measure" from the Values section, the issues comes back when I mix fields from the Masters and SubAccounts tables. There is no performance issue if I only pivot on fields from the same table.
This is clearly a bug in Excel because I cannot recreate the issue in Power BI Desktop.
I see, thank you for the feedback. PivotTable, if no aggregation (other words nothing in Values) returns Full Join of two tables which are in relationship. In your case that's 63,000 x 6,000 = 378 million of values.
That takes time, especially taking into account that, most probably, for major part of the data is used memory cache on hard drive.
- Hi SergeiBaklan thanks for your explanation which has now made me understand the underlying issue.
I guess the Power BI Desktop team have put in additional code to avoid this FULL join.