Time-intelligence functions and aggregations based on group-by columns combined with relationships

[Toerstad]

Hi.

I'm having some trouble with aggregate tables and time-intelligence functions.

On MS Docs, see https://docs.microsoft.com/en-us/power-bi/desktop-aggregations, a situation as illustrated bellow is described.

The SalesAgg-table has relationships to the Product Subcategory and Customer tables, and are grouped on several of the columns in the Date table. Like this:

An example used later in the article is this:

The following time-intelligence query will not hit the aggregation because the DATESYTD function generates a table of CalendarDay values, which is not covered by the aggregation table.

OK – so I need to aggregate on CalendarDay in order to utilize aggregate tables with time-intelligence functions (my interpretation). So in in my case I have now added the CalendarDay as a GroupBy column in the aggregate table on Date[CalendarDay]. This seems to work fine, at least for visuals with a date on the X-axis and a value on the Y-axis. But, when i use time-intelligence functions, like TOTALYTD, or DATESYTD, the query doesn't hit the aggregate table, but the Sales table in DirectQuery-mode.

I've tried to look into this further using DAX Studio.
In a case where I use a "regular"-function like:

EVALUATE
ROW("SUM of SalesAmount",
    CALCULATE(
        SUM(Sales[SalesAmount]),
        FILTER(Date, Date[CalendarDay] = DATE(2019,8,20))
        )
    )

I get an Aggregate Rewrite Attempt: matchFound response. And the engine uses the SalesAgg table to calculate the value. So far so good.

However - when I use a time-intelligence function, like so:

EVALUATE
ROW("TOTALYTD of SalesAmount",
        TOTALYTD(SUM(Sales[SalesAmount]),
        Date[CalendarDay])
    )

The response I get from the rewrite attempt is as follows:

Match Result: attemptFailed
Original table: Date
Mapped To:

Details:

{
	"table": "Date",
	"matchingResult": "attemptFailed",
	"failureReasons":[
		{
		"reason":"no alternate sources"
		}
	],
	"dataRequest":[
		{
		"table":"Date",
		"column":"CalendarDay"
	}
	]
}

Are anybody familiar with this issue? Is there a fix or work-around, or have I done something just plain wrong?

I understand it may have to do with there beeing a lists of dates returned from the time-intelligence functions, but since the Date[CalendarDay] is a group by field in the aggregation shouldnt it work anyway?

I understand a pbix-file would be helpful for troubleshooting, but as this is part of a customer solution I cant just share it. I will however provide you with a file with a reconstruction of the issue when I find the time to make one.

[foresightbi]

Did you ever get this solved?

I'm finding myself in a similar situation and wonder if there's a resolution in the logic or if there's a work-around that's been found!

RSVP

[Toerstad]

Hi. No, I never figured this one out.

The issue was "resolved" for us as we moved from using Power BI to using Azure Analysis Services as the data model layer. With AAS the model size limitations disappeared and we could drop the aggreation tables alltogether. 

I raised a question regarding this at SQL PASS virtual summit in 2020 and it was said to be a known issue, but no solution other than avoiding aggregation tables was suggested.

[Anonymous]

Dear Toerstad

I'm facing the same issue. Did you find a solution/workaround?

Best regards

[v-yuta-msft]

@Toerstad ,

"So in in my case I have now added the CalendarDay as a GroupBy column in the aggregate table on Date[CalendarDay]. This seems to work fine, at least for visuals with a date on the X-axis and a value on the Y-axis. But, when i use time-intelligence functions, like TOTALYTD, or DATESYTD, the query doesn't hit the aggregate table, but the Sales table in DirectQuery-mode."

Could you please explain more about the issue? For example, what does "doesn't hit the aggregate table" mean?

Regards,

Jimmy Tao

[Toerstad]

Sure.

For simplicity lets say I got three tables with the following columns and number of rows.

1. FactValues (600 000 000 rows)
   1. CalendarDay_FK (date)
   2. Value (int)
2. DimDate (3650 rows)
   1. CalendarDay_PK (date)
3. AggValues (3650 rows)
   1. CalendarDay (date)
   2. Value (int)
   3. Count (int)

The DimDate and FactValues tables are related with a "One-To--Many"-relationship.

To avoid having to sum up 600 million rows an aggregation table (AggValues) is added to the model, with a pre-aggregated Value column. 

The AggValues table is defined in the model as an aggeragion table. In the aggregation table the AggValues-table is defined with the follwing summarizations:

Aggregation Column; Sumamrizaton; Detail table;  Detail Column
CalendarDay; GroupBy; DimDate; CalendarDay_PK
Value; SUM; FactValues; Value
Count; Count table rows; FactValues; N/A

Then. When creating a measure such as:

SumOfOneDay = CALCULATE(SUM(FactValues[Value]),DimDate[CalendarDay_PK]=DATE(2019,01,01))

The configuration works. The query engine rewrites the query to target ("hit") the aggregation table AggValues. And instead of summing up millions of rows in the complete fact table FactValues the sum is retrieved from matching the data against one row in the aggregation table AggValues.

However when using a time-intelligence measure, such as this:

SumYearToDate = TOTALYTD(SUM(FactValues[Value]),DimDate[CalendarDay_PK])

The configuration don't work for some reason. The engine is not able to find a match for the column and has to query the complete fact table FactValues (it does not "hit" the aggregation table). Since the FactValues table is fairly large this takes several minutes instead of fractions of a second, as the first one does.