How to Aggregate Large Datasets in Power BI (with Tristan Malherbe)
HTML-код
- Опубликовано: 2 авг 2024
- In this session, Tristan Malherbe (Microsoft Data Platform MVP) will show you how you can leverage Power BI aggregations to analyze big volumes of data in Power BI. Tristan will illustrate the power of Aggregations with the famous New York City Taxi dataset.
GUEST BIO 👤
Tristan Malherbe is the Founder of Data Pulse and a Microsoft Data Platform MVP since 2017.
He is also the co-founder and current co-leader of the French Power BI User Group in France (Club Power BI). His favorite topics are: advanced data modelling, DAX, Data Visualization & performance tuning.
RELATED CONTENT 🔗
Tristan's LinkedIn -- / tristanmalherbe
Tristan's Twitter -- / datatouille
Tristan's RUclips -- / tristanmalherbe
LET'S CONNECT! 🧑🏽🤝🧑🏽 🌟
-- / havensbi
-- / reidhavens
-- / havensconsulting
HAVENS CONSULTING PAGES 📄
Home Page - www.havensconsulting.net
Blog - www.havensconsulting.net/blog-...
Blog Files - www.havensconsulting.net/blog-...
Files & Templates - www.havensconsulting.net/files...
Consulting Services - www.havensconsulting.net/consu...
Contact & Support - www.havensconsulting.net/conta...
EMAIL US AT 📧
info@havensconsulting.net
#PowerBI #powerplatform #microsoft #businessintelligence #datascience #data #dataanalytics #excel #powerapps #datavisualization #dashboard #bi #analytics #dax #pagination #paginated #aggregations 
Наука
![Demystifying [DAX] Evaluation Context (with Brian Grant)](/img/1.gif)
This is by far the best video I've seen on aggregations and dealing with large data sets. Much appreciated.
Very good video on aggregations. Really appreciate it. Feeling very confident to try this.
Great vídeo! Thanks
wonderful video..Great job Tristan and Haven consulting for putting this together.
Interesting! Thanks for sharing this information. Thumbs up!!
This has been fantastically helpful, thank you! Just to say though, the precendence in factors of 10 allows for easy insertion of additional tables in future development/maintenance. 😊
Just what I needed. Thank you!
You're so welcome!
Thank you for this great video! 😊My question is which is more efficient the aggregate table in a composite model or incremental refresh?
Can I set up aggregations with two import tables. My detailed import FACT table has 50M rows. Then I have a product AGG which has 15M. Lastly I have a HRCHY_Agg that has 5M rows. My dax would be so much faster if I could do this. I can't set the 50M row table to direct query because it is frequently referenced and import is way faster. But when I set the detailed fact table to Import I can't seem to set up aggregations.
Hi Noah - great question ! As of today and as I have mentionned in my presentation the detailed table has to be in DirectQuery mode.
You can build your logic by implementing IF statements and managing yourself the different aggregates though.
Great Video, is there any source i can download the presentation files? thank you
Really good presentation. Congratts!
Thanks Rodrigo!
Really interesting talk - thanks!
Glad you enjoyed it!
Super informative !
Glad it was helpful!
Brilliant approach.I have a question, if we create incremental refresh and publish the report we can't downlaod the report/dataset from PBI service and it has to be managed by XMLA end point. Can we edit/modify the agg tables (Manage aggregations part) using XMLA end point using tools like Tabular editor or ALM Tool kit ?
For the mapping of the columns in DQ for an Agg table. I do believe that's only possible in desktop. I even asked a few colleagues and they weren't aware of any way to do that for the model in the service. Vs republishing the PBIX from desktop, and letting the model do a full refresh.
Two other responses actually from Ricardo Rincon
From Ricardo:
docs.tabulareditor.com/te2/Useful-script-snippets.html#setting-up-aggregations-power-bi-dataset-only
Taking advantage of the topic, some time ago there were some comments about the need to allow that the detail table of the aggregations can be import and not only direct query, so I share with you a trick that I learned from Ruben Pertusa Lopez in a conference talk to make aggregations with import detail table (yes, aggregations with import detail table), it does not always work but it is interesting and very simple, the only thing you need is to define an aggregation of precedence 0 with the same fields of the detail table, with this the engine should never have to go to the origin direct query (again I comment that it does not work in all cases) but in the last instance it will look for the data in the detail table import (the aggregation with precedence 0).
www.linkedin.com/in/nexus150/ his LinkedIn if you'd like to reach him
How do you do a distinct count with these agg tables to map it back to your dq fact?
You can use either DAX DISTINCT or Power Query Group By options to create a table with distinct IDs at any level :)
Any videos on how to create those aggregated tables?
A good step by step blog from Reza would be good for that. radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table
🎉❤
How did the number of rows dropped from 300+Mn to 180 k after removin few columns
The grain is still the date ....
Yes but I only have ~ 4 years of data (so ~ 1,460 dates).
My Agg1 is at the date / vendor / rates / Store & Forward flag / Payment type so ... 100k rows totally makes sense !