Aggregations in Power BI One More Thing to Check

I have found one more thing to consider regarding
aggregation tables in Power BI.

A couple of weeks ago I decided to try aggregation tables in Power BI because I have never used this feature before. I researched the topic and found something that I haven’t seen discussed in a video or blog post.

First I read the documentation, https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations then I watched this video

Christian Wade talks about the new feature.

and this video

Configure Power BI Aggregations

then I started creating my report using AdventureWorksDW2017.
On my first try I ran into the mismatched data types problem.
It occurs when you don’t set the data types of the columns according to the fact detail table. Yes, there is a Guy in a Cube video describing the problem.

data types mismatch problem

After setting the data types, I successfully created the aggregation table.

When you are creating the aggregations table in power query you might be wondering what is the all rows operation.
As the documentation describes it makes possible to
expand on the grouping column.

Aggregation table created with Power Query


My two grouping keys were the Date and the ProductKey.

Don’t forget to click on your aggregations table and
select Manage aggregations.

The idea is that if a measure is using the date table or the date and the product table without using the other dimension tables,
the calculations would use the aggregations table.
How can we test whether we are hitting the aggregations table?
Yes, we have a Guy in a Cube video for that.

This is the point when you realized that this is another DAX Studio post, right?

First test:
I have a Bar chart with a Product hierarchy and a Total Sales measure.
I don’t see direct query in the performance analyzer
and I see the black circle in DAX Studio so so everything looks good.

Second test:
The next report page has two visuals, the first is the same bar chart and the second is a column chart with a Geography hierarchy
with Total Sales.

The Geograpy-based chart has a line for direct query in the performance analyzer meaning that the visual is not using the aggregations table.
The white circle represents a query not using the aggregations table.

Third test:
Clicking on a column on the right chart that represents a region,
the bar chart on the left becomes cross-filtered to the same region.
The bar chart is cross-filtered by a dimension that is not using the aggregation table therefore preventing it from using the aggregation.


The first lesson is that we have to take into consideration
the effect of cross-filtering when designing
the report or the aggregations table.

The second lesson is that
the Guy in a Cube YouTube channel is exceptionally helpful
when you want to understand a complex feature of Power BI.