At the time of this writing, Power BI Aggregations are still in preview and actively being worked on. Once they leave preview, I expect this issue will either be fixed, or the limitations will be specified in the documentation, just like with DirectQuery in general.
Currently whenever I try to use a what-if parameter or a disconnected parameter table, Power BI Aggregations don’t work as intended, instead it reverts to Direct Query. Which means if I need to use a parameter of some sort, I can’t get the benefit of using aggregations.
UPDATE: This issue seems to depend on where they are being used. Reza Rad identified that the issue does not occur in an if statement.
UPDATE 2: According to Microsoft, this is intended behavior because the parameters aren’t in the pre-aggregations or the mappings. I’ve created a uservoice ticket for this.
Setup
To reproduce this issue, I’ve made an extremely simple data model based AdventureWorks2014 data. There are 4 tables involved with no direct relationships:
- SalesHeader, which is my fact table, stored in directquery mode.
- SalesHeaderAgg, which is my aggregation table, stored in import mode.
- TerritoryParameter, which is a What If Parameter, generated with DAX
- Territory, which is a disconnected table, stored in dual mode.
I’ve mapped all the columns from my aggregations table to my detail table. In theory, all DAX queries that don’t require a count on CustomerID or TerritoryID, should hit the aggregation table.
To start with, I have a table summing TotalDue by Customer.
I’ve connected profiler to the SSAS instance that Power BI Desktop runs in the background. This allows us to see what is bring run behind the scenes and if it is hitting the aggregation table.
In this case, Power BI Desktop is doing a TOPN:
EVALUATE
TOPN (
502,
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( ‘SalesHeader'[CustomerID], “IsGrandTotalRowTotal” ),
“SumTotalDue”, CALCULATE ( SUM ( ‘SalesHeader'[TotalDue] ) )
),
[IsGrandTotalRowTotal], 0,
‘SalesHeader'[CustomerID], 1
)
ORDER BY
[IsGrandTotalRowTotal] DESC,
‘SalesHeader'[CustomerID]
And looking at the events, we can see a successful query rewrite, with no DirectQuery events. everything looks good.
The problem
Instead of using an implicit measure, let’s use a explicit measure, with a filter based on a parameter field:
Param Total =
CALCULATE (
SUM ( SalesHeader[TotalDue] ),
FILTER (
SalesHeader,
SalesHeader[TerritoryID] = TerritoryParameter[TerritoryParameter Value]
)
)
And at first, everything looks fine. No DirectQuery calls.
But, if I select one of the parameter values using a slicer, now it switches to using DirectQuery.
So what’s the difference? Well in the second DAX query, it’s applying the filter via TREATAS
What if I use an actual table in dual storage mode and just take the MAX instead?
Param Total =
CALCULATE (
SUM ( SalesHeader[TotalDue] ),
FILTER (
SalesHeader,
SalesHeader[TerritoryID] = MAX ( Territory[TerritoryID] )
)
)
Well, I get the same exact DAX pattern and the same result.
Conclusion
Ultimately, this is one of the tradeoffs of using preview functionality. I’m working with the customer to get a ticket escalated with Microsoft. Ultimately, it may just be an intended limitation of the technology. I hope not, though, because aggregations provide for huge performance improvements with minimal effort.
That being said, if anyone has any ideas, I’m all ears! Below is my proof of concept.
