power query to filter the Aggregated Column only

[babbu]

I have table FactSales with columns at weektype-> week->facility->product hierarchy as below:
WeekType (Fiscal Year/ calendar year)
FACILITY_ID,
Product_ID,
UNIT_QTY,
YearID,
WeekID,
yearweek,
SALES_DOLLARS,
COST_DOLLARS

Other dimension table:

• 1 table is facility_dim and has columns like FACILITY_ID, region, division, manager.
• 2 table is product_dim and has columns like Product_ID, category, dept, family.
• 3 table is year-week table - it has columns like weektype( fiscal or ad week), year, week, yearweek

1. Relationship :
   FactSales - facility_dim on FACILITY_ID (1- many)
   FactSales - product_dim on Product_ID (1- many)
   FactSales - year-week on combination of (weektype + yearweek) (1- many)

It worked when all the aggrgations done in power bi and with the realtionships above.

I used direct query to pull the FactSales data because of lot of data (refresh failed otherwise)

But with direct query too has limitation to pull amount of data. FactSales has 9 billion row and power bi crashes when pulling the data even thru direct query

, so eventually I want to use below direct query and use the product_dim and facility_dim attirbutes as filters for that aggregated query.

Here is my aggr query from fact table that I want to use as direct query ( I want to display the aggr numbers based on the slicers)
select
sum(SALES_DOLLARS) as SALES_DOLLARS
,sum(COST_DOLLARS) as COST_DOLLARS
,sum(SALES_DOLLARS)/sum(UNIT_QTY) as SalesPerUnit
,sum(COST_DOLLARS)/sum(UNIT_QTY) as CostPerUnit
,sum(COST_DOLLARS)/sum(SALES_DOLLARS) as %_CostSales
from FactSales
where WeekType = 'Fiscal Week' // use the values selected from single valued slicer from year-week here as parameter//
and YearID = '2025' // use the values selected from single value slicer from year-week here as parameter//
and WeekID in(1, 2...) // use the values selected from multivaluedslicer from year-week here as parameter//
and FACILITY_ID in (11,22,33,...) // use the values selected from multivalued slicer from facility_dim here as parameter//
and product_id in (1,2,3,...) // use the values selected from multivalued slicer from product_dim here as parameter//

I can't use the attributes like year, week , facility, product etc in select and group by , because i think it wil give the same issue, at this hierarchy i have 9 billion rows. 4.5 billion for Fiscal year and 4.5 for calendar year

How do I do this in power bi?

[lbendlin · Accepted solution]

Direct Query is usually limited to 1000000 rows per (intermediate) result, so far below your limit. You need to do aggressive grouping and aggregations to reduce the cardinality.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[v-aatheeque]

Hi @babbu 

Just following up on our earlier conversation regarding the performance issues with your DirectQuery setup on the FactSales table in Power BI.

Given the data volume and aggregation logic involved, @lbendlin previously requested a sample dataset (with representative structure and values) to help us better understand the root cause and offer a more optimized solution.

This will help us simulate your scenario more effectively and provide a solution that addresses both the performance constraints and functional requirements.

Looking forward to your update. Thanks for your collaboration!

[v-aatheeque]

Hi @babbu 

---

Just checking in on our earlier discussion about the performance challenges you're facing with the DirectQuery setup on the FactSales table in Power BI.

To better investigate the issue, we  had requested a sample dataset that reflects your data structure and logic. This will allow us to replicate the scenario more accurately and recommend performance improvements tailored to your case.

Appreciate your collaboration and look forward to your input!

[v-aatheeque]

Hi @babbu 

Just following up on our earlier thread regarding the DirectQuery performance challenges with your large FactSales dataset in Power BI.

As mentioned earlier, @lbendlin had requested a representative sample dataset to help simulate your scenario  particularly with the structure and filter logic you're using  so we can pinpoint the bottlenecks and suggest the most efficient approach.

If we don’t hear back, we may close this thread in line with our community guidelines, but you’re always welcome to post a new query anytime.
Thank you for being part of the Microsoft Fabric Community!

Looking forward to your sample dataset so we can move ahead productively appreciate your time and collaboration!