limit exceeded error with Direct Query

[viswaaa]

Hi All,

I am using a direct query and I have 89000 rows coming from my code.

After I wrote below dax measure I am getting this error.

Please help how to fix this

MOH =

VAR Available = SUM('Query2'[Available to Sell])

VAR MOM_Value = [MOM]  -- assuming MOM is a measure

RETURN

IF(

    Available > 0 && MOM_Value = 0,

    9999,

    MOM_

[burakkaragoz · Accepted solution]

Hi @viswaaa ,

This kind of error usually happens in DirectQuery mode when the query becomes too complex or heavy for the underlying source to handle efficiently.

Looking at your measure:

MOH =
VAR Available = SUM('Query2'[Available to Sell])
VAR MOM_Value = [MOM]

RETURN
IF(
    Available > 0 && MOM_Value = 0,
    9999,
    MOM_
)

A few things to check:

1. Check the [MOM] measure
   If [MOM] is doing any complex calculation or uses CALCULATE, FILTER, or time intelligence functions, it might be generating a very heavy SQL query behind the scenes.

2. Avoid row context inside DirectQuery
   Measures that depend on row-level calculations (especially with large datasets like 89,000 rows) can easily hit query limits. Try simplifying the logic or pre-aggregating the data if possible.

3. Try using IF with ISBLANK() or COALESCE()
   Sometimes using = 0 on a measure can cause issues if the value is actually blank. Try:

   RETURN
   IF(
       Available > 0 && COALESCE(MOM_Value, 0) = 0,
       9999,
       MOM_Value
   )

1. Consider switching to Import mode
   If performance and query limits are a recurring issue, and your data doesn't need to be real-time, switching to Import mode can help avoid these limitations.

Let me know if you can share the [MOM] measure — that might help narrow it down further.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[v-dineshya · Accepted solution]

Hi @viswaaa ,

Thank you for reaching out to the Microsoft Community Forum.

Thank you @burakkaragoz for sharing the response.

Hi @viswaaa , In addition to @burakkaragoz response, i am adding some more points.

1. Pre-Aggregate or Cache Results

If you can switch to Import mode instead of DirectQuery or create an Aggregation Table.

2. Simplify [MOM] measure

Examine [MOM] if it involves CALCULATE, PARALLELPERIOD, or time intelligence over large sets, or use pre-aggregated data.

3. Avoid calculating at high cardinality

Wrap your logic in IF(HASONEVALUE(...)) or limit it to summary-level calculations:

MOH =
IF(
HASONEVALUE('Query2'[Product]),
VAR Available = SUM('Query2'[Available to Sell])
VAR MOM_Value = [MOM]
RETURN IF(Available > 0 && MOM_Value = 0, 9999, MOM_Value),
BLANK()
)

4. Use SELECTEDVALUE Instead of SUM

VAR Available = SELECTEDVALUE('Query2'[Available to Sell], 0)

5. Limit visuals / slicer combinations

Try filtering your data in the report or in your measure using ISFILTERED or IF(HASONEFILTER(...)) to limit evaluation when slicers are too open.

Please refer community thread and Microsoft article.

Solved: Direct Query - Exceeded Limit Unexpectedly - Microsoft Fabric Community

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @viswaaa ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @viswaaa ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @viswaaa ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[burakkaragoz · Accepted solution]

Hi @viswaaa ,

This kind of error usually happens in DirectQuery mode when the query becomes too complex or heavy for the underlying source to handle efficiently.

Looking at your measure:

MOH =
VAR Available = SUM('Query2'[Available to Sell])
VAR MOM_Value = [MOM]

RETURN
IF(
    Available > 0 && MOM_Value = 0,
    9999,
    MOM_
)

A few things to check:

1. Check the [MOM] measure
   If [MOM] is doing any complex calculation or uses CALCULATE, FILTER, or time intelligence functions, it might be generating a very heavy SQL query behind the scenes.

2. Avoid row context inside DirectQuery
   Measures that depend on row-level calculations (especially with large datasets like 89,000 rows) can easily hit query limits. Try simplifying the logic or pre-aggregating the data if possible.

3. Try using IF with ISBLANK() or COALESCE()
   Sometimes using = 0 on a measure can cause issues if the value is actually blank. Try:

   RETURN
   IF(
       Available > 0 && COALESCE(MOM_Value, 0) = 0,
       9999,
       MOM_Value
   )

1. Consider switching to Import mode
   If performance and query limits are a recurring issue, and your data doesn't need to be real-time, switching to Import mode can help avoid these limitations.

Let me know if you can share the [MOM] measure — that might help narrow it down further.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI