Reply
POSPOS
Post Patron
Post Patron
Partially syndicated - Outbound

"Query has exceeded the available resources" issue even for one row of data in a table

Hi All,

I have model in Power BI as below.

POSPOS_1-1734881910689.png

The first table is connected to the below three tables on different fields.

Based on the three tables, I have created bridge tables for each field so that I can get a combination of table from the three tables in my final output table.

Each bridge tables are then connected to the three main tables.

Now the issue is, when I pull the data from each bridge table and use a measure with a simple calculation from one of the three main tables, it gives me data exceeded error even when the output is just one row.

POSPOS_3-1734882085361.png

the calculation in the measure is a simple one : Example below. Even a simple agrgregation does not work

Total Headcount (Table 2) = 
var headcount = CALCULATE(DISTINCTCOUNT('Proposed Budget(Same FC_Same Org)'[ID]))
RETURN 
IF(ISBLANK(headcount),0,headcount)

Applying filters and restricting to minimum data also gives the same issue.

Can someone please suggest why this is happening?

Thank you

 

 

6 REPLIES 6
v-tangjie-msft
Community Support
Community Support

Syndicated - Outbound

Hi @POSPOS ,

 

Whether the advice given by Idrissshatila has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

 

Best Regards,

Neeko Tang

Idrissshatila
Super User
Super User

Syndicated - Outbound

Hello @POSPOS ,

 

"even for one row of data in a table", bro you're having bi-directional many to many relationships.

 

this model should be re worked again in the best way, check the concept of star schema and try to implement it.

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Syndicated - Outbound

Hi @Idrissshatila  -  We only have single directional joins between tables with one to one or one to many cardinality. We do not have bi-directional joins between any tables 

POSPOS_0-1734886091381.png

 

Syndicated - Outbound

Hello @POSPOS ,

 

so what's happening within your dax measure is that it's giving the values that are blank a value which is zero, thus it's getting too much data that is exceeding the query limit resources.

 

I suggest to remove this 0, or filter out some data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Syndicated - Outbound

@Idrissshatila  - That was tried too. Data was filtered out and also tried excluding zero's. Still the same issue,

Syndicated - Outbound

@POSPOS ,

 

then you should rework the model in an efficient way, according to best practices.

 

- Avoid many to many relationships.

- build star schema model.

- Remove unnecessary columns.

- Optimize the use of numeric data types.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)