setting management aggregation then insufficient memory in powerbi desktop

[Anonymous]

When setting management aggregation, there are too many columns (400 +), and powerbi desktop becomes particularly slow, which directly popup reports that there is insufficient memory.

how can I solve this problems?

looking forwad to your reply.

thanks a lot.

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

When using DirectQuery, the overall experience depends very much on the performance of the underlying data source. If possible, try changing the storage mode of this large table from Direct Query to Import. Note that this action is irreversible.

Follow best practices for using import versus DirectQuery:

You should import data to Power BI wherever possible. Importing takes advantage of the high performance query engine of Power BI, and provides a highly interactive and fully featured experience.
If your goals can't be met by importing data, consider using DirectQuery. For example, if the data is changing frequently and reports must reflect the latest data, DirectQuery may be best. However, using DirectQuery is only feasible when the underlying data source can provide interactive queries, less than 5 seconds for the typical aggregate query, and can handle the query load that will be generated. Additionally, the list of limitations for the use of DirectQuery should be considered carefully.
The set of capabilities offered by Power BI for import and DirectQuery evolve over time. Changes will include providing more flexibility when using imported data, such that import can be used in more cases and eliminating some of the drawbacks of using DirectQuery. Regardless of improvements, when using DirectQuery, the performance of the underlying data source always remains a major consideration. If that underlying data source is slow, using DirectQuery for that source will remain unfeasible.

Using DirectQuery in Power BI - Power BI

User-defined aggregations

Here are some references that may be helpful.

How Aggregation Tables Improve Performance for Power BI Reports

Power BI Storage Modes for Aggregations

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

You can use the property pane in the Model view to update multiple columns.

Or you can try to use tabular editor , if that can help

Appreciate your Kudos.

[Anonymous]

hi, amitchandak,

thank you for your reply.

This is the case that there is a large table with 400+columns , with DQ mode to connect it, meanwhile,

I import an aggregation table to  powerbi desktop, creating hybrid model to optimize performance.

when I setting management aggregation, powerbi desktop became very slow. It takes a long time to click a drop-down menu to set 400 + cloumns. Is too many columns causing too slow?