Aggregations in Power BI

on YouTube ↗51m

" summary

Shows how Power BI rewrites slow DirectQuery SQL into smaller pre-aggregated tables and when that succeeds or fails, using DAX Studio performance traces and Power BI Performance Analyzer to see “match found” rewrites. Builds sales_by_date and sales_by_customer_date in SQL (recommended as a materialized view in SQL Server), sets aggregation mappings and precedence, and converts row-level pricequantity into a calculated column for line amount* so SUM can match. Some queries rewrite and run in-memory fast; others fail when required dimensions (product brand) aren’t in the aggregation or when imported aggregates become stale.

AI-written summary · about

" chapters (9)

1. 0:00Aggregations in Power BI
2. 4:07Power BI engine architecture
3. 12:59Manual aggregations in DirectQuery
4. 28:48Complex measures with aggregations
5. 34:40Aggregation types
6. 38:56Switching to Import mode
7. 45:26When to use aggregations
8. 49:00Automatic aggregations limits
9. 50:25Conclusion

" related talks

• 51mPower BI Composite and Hybrid ModelsAlex Whittles · SQLBits 2023
• 45mAzure DW and Power BI, reporting at the speed of light!Rita Dias, Stijn Wynants · SQLBits 2020
• 50mThe Path to Success with Power BI: Best PracticesPatrick LeBlanc, Adam Saxton · SQLBits 2023
• 51mTroubleshooting Power BI Report PerformanceBenni de Jagere · SQLBits 2020
• 21mThe Power BI Engine in 20 minutesKasper de Jonge · SQLBits 2023
• 48mTime Intelligence in Power BIThomas LeBlanc · Power Platform Virtual Conference 2021
• 58mPower BI for the DeveloperPeter Myers, Chris Webb · NDC London 2023
• 51mData modeling for experts with Power BIMarc Lelijveld, Jeroen ter Heerdt · SQLBits 2023