Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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.
User | Count |
---|---|
102 | |
91 | |
85 | |
77 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |