Forum Discussion

Toerstad's avatar
Toerstad
Icon for Advocate I rankAdvocate I
7 years ago

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 ...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Toerstad
    Icon for Advocate I rankAdvocate I
    7 years ago

    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.