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

[POSPOS]

Hi All,

I have model in Power BI as below.

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.

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

[v-tangjie-msft]

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]

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

[POSPOS]

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 

[Idrissshatila]

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.

[POSPOS]

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

[Idrissshatila]

@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.