For ensuring the top performance of your Power BI dashboard, you need to pay particular attention to data modeling.
Here are the most recommended practices that you need to take into account:-
- Do not use wide tables – There a lot of issues that can crop up because of the use of wide tables.
The most common issue is simply performance. In a wide table, you are adding redundant values, again and again, so you are not making the most optimum use of your memory. In case your dataset keeps growing you can simply run out of memory
Other issues that can crop up due to wide tables is building relationships between two wide tables, you can end up with bi-directional and M-N relationships that can be difficult to work with.
To overcome these issues, it is recommended highly to use a star schema or a constellation schema for the design of your data warehouse.
2. Choose only the columns that you really need – This recommendation might sound simple, but is a very important aspect of data modeling.
The more unnecessary columns you add in your table, the more space it will take and will slow the report down
3. Perform Data transformations before Power BI – There are three places where you can apply data transformation once it is in the original source, then you have the query editor and DAX.
For optimum performance, if you have access to the source and can make changes there do so, this can be easy in the case of a data warehouse. This can be accomplished easily if the source is a database or a file.
If you cannot make changes in the source then do in the query editor, it may add some latency to the refresh process, but is manageable.
If the data that needs to be combined is from two different sources then you have no choice but to make the calculations using DAX. But try not to do complicated calculations in fact tables with many rows.