User Defined Aggregations with Time Intelligence

[JamesMcEwan]

I’m using the “accordion aggregation” pattern from Phil Seamark’s post:“ Creative Aggs Part III – Accordion Aggs”.

https://dax.tips/2019/10/25/creative-aggs-part-iii-accordion-aggs/

Setup (simplified):

• Tables: factSales (DirectQuery) and factSales_agg (Import).

• factSales_agg has an AggDate column that anchors each day to the start of month to reduce ~30 daily rows to 1 per month.

• In Manage Aggregations, I map measures from factSales_agg to factSales, and set GroupBy to include factSales_agg[AggDate] (and other dimension keys).

• Date table is related in the standard way (marked as Date).

What works:

• When visuals group by Month (i.e., using the month anchor), queries correctly hit factSales_agg (Import).

Problem:

• When I put dimDate[Date] (daily grain) on a visual, the values are still aggregated to the month start, and the engine continues to use factSales_agg rather than falling back to factSales (DQ). I expected daily visuals to bypass the agg and query the DQ fact.

Question:

• How can I configure the model so that:

  • Monthly visuals use the Import agg (good), but

  • Daily visuals automatically fall back to the DQ fact?

I was considering adding an AggDate to factSales and using that in GroupBy, but I’m unsure whether that’s the right approach, and whether it plays nicely with time intelligence (YTD/MTD) built on the canonical Date table.

Desired behavior (summary):

• Month-level → Import agg

• Day-level → DQ fact

Any guidance on the correct GroupBy keys/relationships to achieve this (and still keep standard time intelligence working) would be appreciated.

[v-tejrama · Accepted solution]

Hi @JamesMcEwan ,

The reason you're seeing this behavior is that your aggregation table is still in use when you add the daily date column to your visual. Power BI will use an aggregation if it can fully answer the query, and in your model, the month anchor column in the aggregation table remains valid for daily queries. Changing the relationship from the Date column to the Month wouldn't resolve this and would actually result in a many-to-many relationship, which isn't supported for aggregations.

To address this, keep your main Date table as it is for time intelligence and daily reporting. Add a separate Month table with one row per month and a MonthStart column. Relate this Month table to the AggDate column in your aggregation table and set up your aggregation mappings accordingly. Month visuals will use the Import aggregation via the Month table, while daily visuals based on the Date table will default to the DirectQuery fact table. This setup ensures clear, consistent behavior without affecting your time intelligence calculations or creating unsupported relationships.

Thank you.

[parry2k]

@JamesMcEwan with calendar are you using month or date for aggregation, looks like you are using date

[JamesMcEwan]

@parry2k - The Calender is on the Date, should I move the relationship to the Month? This would be a many → many relationship and this isn't allowed for aggregations.