Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is optimizing a Power BI report with several complex measures and calculated columns. The report is experiencing slow refresh times and sluggish interaction. Upon reviewing the model, the modeler notices that many calculated columns perform aggregations across large tables. Which of the following actions would most effectively improve the model's performance?
- AAdd more indexes to the underlying data source tables.
- BConvert calculated columns that perform aggregations into measures.
- CChange the storage mode of all tables to 'DirectQuery'.
- DIncrease the data refresh frequency to distribute the load.
Show answer & explanationAnswer & explanation
Correct answer: B. Convert calculated columns that perform aggregations into measures.
Calculated columns consume memory and are computed during data refresh for every row, even if not used in visuals. Measures, on the other hand, are calculated on-the-fly at query time and only for the specific filter context, making them more efficient for aggregations.
Why the other options are wrong
- A. Adding indexes to the source database helps with initial data loading/DirectQuery performance, but not directly with DAX calculation performance within the Power BI model itself for imported data.
- C. Changing to DirectQuery would shift processing to the source database but often results in slower report interaction compared to Import mode for complex calculations.
- D. Increasing refresh frequency would worsen performance, as it triggers the re-calculation of all calculated columns more often.
Calculated Columns vs. Measures
Calculated columns store values for each row in the model and are computed during data refresh, consuming memory. Measures are calculated on-the-fly at query time based on the current filter context and do not consume memory for storage.
- Calculated columns: row-level, stored in model, refreshed with data.
- Measures: aggregated, calculated at query time, context-dependent.
- Use measures for aggregations to optimize performance.
- Use calculated columns for static row-level attributes (e.g., age from birthdate).
Memory trick: Optimize by thinking 'measure first' for aggregations, not 'column always'.