A data modeler is optimizing a large Power BI model with several disconnected tables used for 'what-if' analysis and scenario planning. These tables contain parameters that users can adjust. The model is experiencing slow query performance when interacting with visuals that incorporate these parameters. Which of the following modeling practices would most effectively improve the performance of such a model?
- AUse TREATAS or VALUES functions to apply filters from disconnected tables to fact tables.
- BConvert disconnected tables into calculated tables to pre-process parameter values.
- CMerge disconnected tables with fact tables in Power Query to create a single, wide table.
- DEnsure all relationships are active and bidirectional to allow filters to flow freely.
Show answer & explanationAnswer & explanation
Correct answer: A. Use TREATAS or VALUES functions to apply filters from disconnected tables to fact tables.
Disconnected tables do not have active relationships with other tables, so their filters do not propagate automatically. To apply filters from a disconnected table to a fact table, DAX functions like TREATAS or VALUES must be used explicitly within measures. TREATAS is particularly efficient for applying a table expression as filters to columns of another table, effectively creating a 'virtual relationship' for the duration of the query without the performance overhead of physical relationships.
Why the other options are wrong
- B. Converting disconnected tables to calculated tables doesn't inherently solve the filtering problem; they would still be disconnected from fact tables unless explicitly linked by DAX.
- C. Merging disconnected tables with fact tables would result in massive, denormalized tables, leading to significant memory consumption and slow refresh times, especially with large fact tables. This defeats the purpose of disconnected tables for flexible 'what-if' scenarios.
- D. Disconnected tables, by definition, lack relationships. Creating bidirectional relationships everywhere can lead to ambiguity and performance issues, especially in complex models, and isn't applicable to truly disconnected tables for 'what-if' scenarios.
Disconnected Tables & TREATAS
Disconnected tables (or parameter tables) are tables in a Power BI model without active relationships to other tables. They are typically used for 'what-if' analysis. Filters from these tables are applied to measures using DAX functions like TREATAS or VALUES to establish a virtual relationship for the duration of the query.
- No active physical relationships.
- Used for dynamic parameter input or 'what-if' scenarios.
- Filters do not propagate automatically.
- TREATAS and VALUES are key DAX functions to apply filters.
- Improves model flexibility and reduces cardinality.
Memory trick: Disconnected tables, TREATAS connects the dots for 'what-if' scenarios.