Power BI Best Practices AI Prompt

Apply Power BI best practices to build a robust, performant report.

Report goal: {{goal}}
Data model: {{data_model}}
Power BI version / license: {{license}}

1. Data model best practices:
   - Star schema: always model as star schema — fact tables with foreign keys to dimension tables. Never use flat tables with denormalized data.
   - Relationships: one-to-many only in the model — many-to-many relationships cause performance problems. Resolve many-to-many with a bridge table.
   - Import vs DirectQuery: use Import mode for anything not requiring < 1-hour freshness. DirectQuery limits DAX features and is slow.
   - Composite model: use when some tables must be real-time (DirectQuery) and others can be imported.

2. DAX best practices:
   - Use variables (VAR ... RETURN): improves readability and performance by evaluating an expression only once
   - Avoid CALCULATE inside iterators (SUMX, COUNTX): nested iteration is slow
   - Time intelligence: always use a proper Date table marked as Date table in the model
   - Measures vs calculated columns: always prefer measures for aggregations. Calculated columns are stored in memory; measures compute on demand.

3. Report design:
   - Page tabs: meaningful names, not 'Page 1, Page 2'
   - Slicer placement: top or left of page, never inside the report body
   - Visual interactions: configure interactions explicitly — by default every visual cross-filters every other
   - Bookmarks: use bookmarks for show/hide panels, not for navigation (navigation pages are better)
   - Tooltips: create tooltip pages for rich hover details

4. Performance optimization:
   - Turn off 'Auto date/time': in Options > Data load. Auto date creates hidden date tables for every date column — major performance killer.
   - Reduce cardinality: avoid high-cardinality text columns in the fact table. Use integer keys and dimension tables.
   - Disable cross-highlighting on visuals not requiring it: each cross-highlight = a DAX query
   - Use Performance Analyzer (View > Performance Analyzer) to identify slow visuals

5. Governance:
   - Deployment pipelines: use Dev → Test → Production pipeline for enterprise reports
   - Row-level security (RLS): implement in the model, not in the report layer
   - Naming conventions: [Measure Name], d_DimensionTable, f_FactTable

Return: data model validation checklist, DAX pattern examples for this use case, performance optimization steps, and naming conventions.