I have a weekly data set at the item level by geography (yes, I could likely import this but testing some different options). Will aggregate tables work when my main table is a direct query related at the item level if I create aggregate tables by certain levels of my data selection? For example, one table that is aggregated by department, one by category, and one by subcategory. My goal would be to divert direct querying until a client selected a brand or product as GBQ would have less filtering and aggregating to do at that point.

Am I understanding aggregate tables correctly? When I test this in DaxQuery Editor it seems like at a department or category selection it is still running a SQL code (albiet it is only ~1700 ms run time). I thought that Power BI would determine it needed to use the imported aggregation table before moving to the DQ table.

I don't want to break up my dimProduct table as I want to be able to create nested slicers at various product levels. Any help would be great!