Forum Discussion

TMJForrester's avatar
TMJForrester
Icon for Advocate II rankAdvocate II
1 year ago
Solved

User-Defined Aggregations with Power BI Dataset as DQ Source

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...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

     

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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

 

TMJForrester's avatar
TMJForrester
Icon for Advocate II rankAdvocate II
1 year ago

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's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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.