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
and this video
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.
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.
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?
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.
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.
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.