Aggregation Table Not Working with Year Filter in Power BI

[_montyburns]

I've encountered an issue with my Power BI report where an aggregation table is not being utilized correctly when I apply a year filter in my visual. I would appreciate any insights or advice on how to resolve this.

Context:

I have a data model with a fact table that spans 4 years (2020-2023) of data and over 1 billion records. To improve performance, I've created an aggregation table that contains 2 years (2022-2023) of aggregated data.

The issue arises when I use a slicer based on the Year column from my Dim_Calendar table. When selecting a year that is beyond the 2 years covered by the aggregation table (i.e., a year from the remaining 2 years in the fact table), I expect Power BI to bypass the aggregation table and revert to the detailed fact table. However, it's not happening; the visual instead shows blank as if there's no data available.

Simplified Data model:

• Fact Table: ecommerce_global with a report_date column among other measures.
• Aggregation Table: ecommerce_agg with aggregated data, also with a report_date column.
• Dimension Table: Dim_Calendar with date, Year, Quarter, and other related time columns.

Relationships are correctly established between ecommerce_global ↔ Dim_Calendar and ecommerce_agg ↔ Dim_Calendar, with report_date and date as the relationship keys.

Issue:

When selecting a date range in the slicer that falls within the last two years (covered by the aggregation table), everything works fine. When selecting a date range in the slicer that is older than the last two years (not covered by the aggregation table), the visual goes blank.

Conclusion:

In DAX Studio I see that every time I select an year prior to 2022, the AGG is still hit, wish is not expected.

Would appreciate any kind of tip to try and circumvent this issue!

[_montyburns · Accepted solution]

I've come to a workaround, mainly because, as far as I could understand the engine does not work how I imagined it.

Your aggregation table must encompass the full dataset of the facts table. Even though the engine is very good, he's not able of switching between AGG and Facts table, if the AGG does not have all of the required timeframe.

The solution was to create a data model based on a hot and cold direct query:

The HOT DQ will encompass data from the last 2 years, while the COLD DQ will encompass data prior to the last 2 years.

I've had to make a few adjustments on the measures I've created, for example:

Measure =
 DIVIDE(
  CALCULATE(
   COUNT(HOT_cci_gpmt_ecommerce_global[article]),
   HOT_cci_gpmt_ecommerce_global[has_discount] = 1
 ),
  CALCULATE(COUNT(HOT_cci_gpmt_ecommerce_global[article]))
 )
 +
 IF(
   MIN(Dim_Calendar[date]) < [_min_updated_date],
   DIVIDE(
    CALCULATE(
     COUNT(COLD_cci_gpmt_ecommerce_global[article]),
     COLD_cci_gpmt_ecommerce_global[has_discount] = 1
   ),
    CALCULATE(COUNT(COLD_cci_gpmt_ecommerce_global[article]))
 )
)

The measure containing the _threshold_date, required to evaluate if the consulting of the COLD DQ is required:

_threshold_date =
 CALCULATE(
  MIN(HOT_cci_gpmt_ecommerce_global[report_date]),
  ALL(Dim_Calendar)
 )

This way:
* if the user selects the last 2 years, the AGG is hit;
* if the user selects a timeframe prior to the last 2 years, the COLD DQ is hit;
* if the user selects a timeframe encompassing the last 4 years (for instance), both the AGG and COLD DQ are hit.

Hope i was able to explain it well, and thank you for the help!

[DataInsights]

@_montyburns,

I'm not aware of a way to achieve this with delivered functionality, but I wonder if a smart measure such as the one below would work. It would require adding a second instance of the DirectQuery fact table and relationships (DQ tables have a small footprint). This second fact table would not use Manage Aggregations. The concept is to see if the AGG table contains data for the specific DAX query, and then use the appropriate table. Alternatively, you could use ISBLANK ( [Primary Measure] ) instead of ISEMPTY (see which one performs better). Let me know if this works.

Smart Measure =
IF (
    ISEMPTY ( 'cci gpmt AGG' ),
    [Secondary Measure],
    [Primary Measure]
)

Primary Measure = SUM ( 'cci gpmt ecommerce'[Column To Sum] )

Secondary Measure = SUM ( 'cci gpmt ecommerce 2'[Column To Sum] )