They include inefficient M code in the Power Query and DAX queries that are not optimized. Here are some of the common mistakes and solutions to help mitigate them.
Eliminating Unneeded Steps and Columns: In Power Query, each transformation step increases the time taken by the processing engine. The performance can also be significantly improved by eliminating unnecessary processes and steps, aggressive filtering of rows after early stages, and vertical reduction of columns to the bare minimum needed for analysis. Loading only required data minimizes memory consumption, therefore ensuring quick processing. In the same vein, do not bring in entire tables when only certain columns or rows are of interest - this simplifies the query and improves the speed of the report.
DAX Calculation Optimization: One key area that may affect speed is the quality of the DAX queries used. One frequent error is when performing ‘row by row’ calculations by means of functions such as FILTER or CALCULATE, which work on voluminous tables. Instead, use aggregate data in its source when practicable. However, measures should refrain from applying functions that have the variable attribute, such as NOW and TODAY, as they tend to recalculate every time there’s a change. It is also possible to use variables (VAR) in DAX on different calculations that occur within the same measure and utilize the stored value instead of recalculation, leading to less processing time.
Diminishing Complexity of Data Models: Complex data models, especially those that are heavily interlinked with many two-way relationships, risk compromising the performance of reports. The performance of the data model structure may be enhanced by eliminating redundant tables or utilizing one-directional relationships where possible. Furthermore, creating and utilizing summary tables for data with high cardinality, like transactional data for visual aids made up of these tables, can also ease the processing unit for such reports, hence reducing the time taken to load the reports.
Optimizing the steps in Power Query, DAX logic, and the data model itself can improve report performance and provide users with a more comfortable experience.
