Aggregations used in new measure (Composite Model)

[AUaero]

Hi,

I've got a composite model with an aggregation table as described here (https://docs.microsoft.com/en-us/power-bi/desktop-aggregations).  The aggregations work great for providing summary data against my direct query table.  Here's my question - I want to calculated a running total for one of the fields that is being aggregated in my aggregate table.  Does Power BI use the value from the aggregate table or does it cause a query against the data source?

Example -

Direct Query table = FactDQ, columns Workcenter, Date, Order Number, Customer Number, Labor_Hours, Setup_Hours

Aggregate Table = FactAgg, columns Workcenter, Date, Labor_Hours, Setup_Hours

FactAgg was created in Power Query by using Group By to summarize the data from FactDQ over Workcenter and Date.

Measures -

# Labor Hours  = Sum(FactDQ[Labor_Hours])

# Labor Hours Cumulative = Calculate([# Labor Hours], Filter(All('Calendar'[Date]), 'Calendar[Date] <= Max('Calendar'[Date]))

Since I'm using the aggregation feature, when my Power BI report queries # Labor Hours, it returns a result from the aggregate table rather than the direct query table.

To restate the question - when # Labor Hours Cumulative calculates, since it's referencing a measure that is being fed by the aggregation, does this trigger a query to the underlying data source, or is the query answered from the aggregate table?

Thanks!

[v-joesh-msft]

Hi @AUaero ,

This depends on the storage mode of your aggregate table. If you set the storage mode of FactAgg to Direct Query, it will trigger a query to the underlying data source, If you set the storage mode of FactAgg to Import, the query is answered from the aggregate table.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.