Forum Discussion
limit exceeded error with Direct Query
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
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:
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.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.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 )- 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 AIHi 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
5 Replies
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:
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.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.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 )- 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 AIHi 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
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
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