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. 😊
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.
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).
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 !
