allow user-defined aggregations with powerbi services datasets

[stephiemomo]

Hello Team PowerBI,

I come to you because I use PowerBI and I recently set up user-defined aggregations from PowerBI desktop.

I think I've done everything correctly, but the visuals are still loading slowly and don't seem to detect that they need to use my aggregation table.

And even using the traces on DAX STUDIO, I don't have any events that allow me to say that the aggregations are detected and used.

My data source is of type “PowerBI services datasets” that I connected in DirectQuery. So I'm wondering if PowerBI Services Datasets support manually defined aggregations by users? If yes, why is it not working for me?
If not, it would be great to allow users to manually define aggregations based on their powerBI services datasets

My PowerBI Desktop release: 2.116.966.0 64-bit (April 2023)

My Dax Studio release: 3.0.6 (2023-03-14)

Thanks team!

[v-yanjiang-msft]

Hi @stephiemomo ,

Sorry I'm not quite clear about what "user-defined aggregations" means, would you like give an example or the steps how can I reproduce it.

Best regards.

Community Support Team_yanjiang

[stephiemomo]

Hi @v-yanjiang-msft 

Thank you for your response to my post.

My goal is to allow the visuals in my reports to update their content more quickly. To do this, I opted for the Power BI feature that involves manually configuring aggregations in Power BI Desktop. According to the documentation, this involves connecting to the detail table in Direct Query mode, then connecting to the aggregated data table in Import mode, mapping the necessary columns between the aggregated table and the detail table, and then producing the desired visuals. When done correctly, Power BI should be able to determine which data it needs to feed the visuals.

To achieve this, here are the steps I followed:

1. Creating a new report file in Power BI Desktop.
2. Connecting to my dataset on Power BI Service: Get Data tab -> Power BI datasets -> Select dataset -> Add a local data model. This step connects me to my Power BI dataset in Direct Query mode.
3. Creating a table with the necessary aggregated data in Excel.
4. Importing my aggregated table (Excel file) into my Power BI Desktop data model

My aggregation table

My details table

The configuration of the mapping between the aggregation table and the detail table.

Result: the response time is still not optimal, as if Power BI is not able to detect that it needs to use the aggregated table for this visual. However, I think I have configured everything correctly.

Note: the aggregated table (Aggr) only has 499 rows and 7 columns.

So I'm wondering if I missed something or if user-defined aggregations are simply not (or not yet) taken into account for Power BI service datasets connected in DirectQuery mode?

@v-yanjiang-msft 

[v-yanjiang-msft]

Hi @stephiemomo ,

May I know where did the below dialog come from?

Best regards,

Community Support Team_yanjiang

[Stéphie]

Hi @v-yanjiang-msft , 

Once in the "model" tab of PowerBI Desktop, I have access to this dialog box by right-clicking on my aggregation table (Aggr) and then choosing the "manage aggregations" option.

@v-yanjiang-msft 

[stephiemomo]

Hi @v-yanjiang-msft , 

Once in the "model" tab of PowerBI Desktop, I have access to this dialog box by right-clicking on my aggregation table (Aggr) and then choosing the "manage aggregations" option.

@v-yanjiang-msft