Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Toerstad
Advocate I
Advocate I

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

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.

combining aggregation techniques

 

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:

Sales Agg aggregations table

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.

query example

 

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.

5 REPLIES 5
foresightbi
Advocate I
Advocate I

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

 

 

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
Not applicable

Dear Toerstad

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

Best regards

v-yuta-msft
Community Support
Community Support

@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

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors