User-Defined Aggregations with Power BI Dataset as DQ Source

[TMJForrester]

Does anyone know if it's possible to create a user-defined aggregation, where the Direct Query source is another Power BI dataset? All the previous forum posts I've read about this weren't very clear on answering this question, so I was hoping to get a definitive answer from someone who knows whether or not this is feasible (and hopefully save other people some time in case they also want to know).

My gut says no, but now that DQ over PBI datasets/AAS models has been GA for quite a while, my fingers are crossed that I'm wrong. Personally, I'd love the ability to aggregate my very large enterprise models in a specific report, especially in cases where that report only needs a small amount of data from the enterprise model.

User-defined aggregations - Power BI | Microsoft Learn

Announcing general availability for composite models on Power BI Datasets and Analysis Services mode...

[danextian]

Hi @TMJForrester 
I don't think that it's possible. There's no option to manage aggregations on DQ semantic model.

[TMJForrester]

Aren't you supposed to click Manage Aggregation on the imported table? I might be wrong, but I think you're trying to look for that option on the Direct Query table.

To be clear, this question is relevant for a composite model. Below is a screenshot of a sample data model diagram if that helps. Note the tables with the blue highlights are DQ from another Power BI dataset, and the "Aggregation" table on the left is an imported table.

[danextian · Accepted solution]

Sorry. My bad as it is possible to use summary SQL view as the aggregation table. Now, based on testing it seems possible. I used a summary imported table as the agg table and check on the DAX query generated. A portion of it is referencing the aggregation table but I did use the column the DQ-AAS table.

GROUPCROSSAPPLY(
		TREATAS(DEPENDON(_Var2, EARLIER(_T39[Value], 2)), 'f_AggTable'[Last Characters]),
		GROUPCROSSAPPLY(
		TREATAS(DEPENDON(_Var2, EARLIER(_T39[Value], 2)), 'f_AggTable'[Last Characters]),
		"__Agg0", SUMX('f_AggTable','f_AggTable'[Amount])
	)"__Agg0", SUMX('f_AggTable','f_AggTable'[Amount])
	)
)

After disabling the aggregation, these lines disappeared. The visual also refreshed and was noticeably slower.