Organising my tables and calculations to help performance

[EllenW]

Hello everyone.

I have a Power BI file with about 30 tables which come from a seperate database.

I'm wondering on what would be the best way to organize them in a way that will help it run smoothly.

The main visuals will be standard table visual. One table per page. Would it be best to create a table for each visual and look up the values from the other tables? Or would it be better to set up relations between the tables and make the visuals that way using the primary key?

I will also need to make various calculations with the data accross the raw data tables. Is it best to pull the raw data into a new table and then calculate with that or create a new column in a raw data table that calculates the result? Or another solution?

(And would it then be better to use measures or to use calculated columns?)

Thanks

[v-yuta-msft]

@EllenW ,

I would suggest you read blogs and doc below about how to improve report performance:

https://docs.microsoft.com/zh-cn/power-bi/guidance/power-bi-optimization

https://www.sqlgene.com/2019/09/27/a-comprehensive-guide-to-power-bi-performance-tuning/

You can also use the performance analyzer to analyse the performance or the visual, measure, columns.

https://www.sqlbi.com/articles/introducing-the-power-bi-performance-analyzer/

Community Support Team _ Jimmy Tao

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

[Greg_Deckler · Accepted solution]

Very difficult to say because I have no concept of those 30 tables. Calculated columns are great to do pre-processing and avoid any slowness caused by complex measures. Also, calculated columns have the added benefit of tending to be more self-service friendly.

Beyond that, star schemas tend to be good data models. If you can merge some of those tables into a big wide fact table that would be great to avoid any calculations and shove the processing to Power Query.

[amitchandak]

Combine the data, where ever possible. Create a common dimension. And a date dimension. Try to make sure you are ending up into star schema. Avoid Many to Many and Bi-directional join , unless most needed.

Refer:https://docs.microsoft.com/en-us/power-bi/guidance/

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-Query-Collect-combine-and-transform-da...