User Defined Aggregations - Direct Query with Power BI Dataset and Azure AS

[TMJForrester]

Hi all,

Is it possible to create a user-defined aggregation from a Power BI dataset/Azure Analysis Services model connected via DirectQuery?

It seems like the option is available to use (as an example, I have a report connected to an AAS model via DQ, a separate aggregation table imported, and the "Manage Aggregations" option works like normal). But after running a trace in DAX Studio's Server Timings tab to test my aggregation, the query trace doesn't show that a match was found. I've tried the aggregation with both defined relationships and GroupBy columns, but no such luck.

Has anyone tried using aggregation tables with DirectQuery for PBI datasets/AAS before? Or is this only available for traditional DQ sources like SQL Server?

Using DirectQuery for datasets and Analysis Services (preview) - Power BI | Microsoft Learn

[stephiemomo]

I'm facing exactly the same situation and have no choice but to go through PowerBI datasets. Has anyone found a solution to this problem please? I am a taker for any advice.

[lbendlin]

Aggregation tables are supposed to be based on import mode.

[TMJForrester]

My aggregation table is imported into my model - the rest of the tables in my model (including my main Fact table) are using DirectQuery mode. Isn't that how user-defined aggregations are supposed to work? Or am I misunderstanding something?

[lbendlin]

That is correct - in the vast majority of scenarios.  There are corner cases where you need to do it the other way round.

If the query engine didn't engage the aggregation table then you must have some query filters that interfere.

This article may help User-defined aggregations - Power BI | Microsoft Learn