Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
84 | |
77 |