Forum Discussion
Helper IIIPower BI DirectQuery: Child table shows exceeds 100,000 row error after adding applying a measure
I have two visuals in a report:
Session (parent table)
Joblet (child table)
There’s a 1-to-many relationship from Session → Joblet.
the measure to control filtering and applied to joblet visual:
IsSessionSelected =
IF(
ISFILTERED(SESSION_TRANS[Session ID]),
1,
0
)Then I applied a visual-level filter on the Joblet visual:
only show if IsSessionSelected = 1
The idea is that the Joblet table should only show rows when a session is selected.
So if I haven’t clicked any row in the Session visual, the Joblet table should ideally be empty.
However, I’m seeing this error:
“Resultset of a query to external data source exceeded the maximum allowed size of 100000 rows.”
I don’t understand why this happens — it seems like it should just return 0 rows since no session is selected.
However, when I removed this IsSessionSelected = 1, the error disappear and it just show up all the joblets records.
Could someone give me ideas on why this happens and how to fix this?
Hi Zhinee , Thank you for reaching out to the Microsoft Community Forum.
It looks like the key issue is that your Session filter isn't propagating its filtering effect to the Joblet table, even though you'd expect a parent-child relationship to do this. This often happens in DirectQuery mode if there's a disconnect in the relationship setup or because your filters and visual-level logic (such as with custom measures) don't trigger the automatic propagation. If the Joblet table isn't properly linked or the relationship isn't enforced (for example, with referential integrity or correct cross-filter direction), the child table visual can end up displaying all records or run into row-limit errors when a measure filter is applied.
Double-check your relationship settings. Enabling Assume Referential Integrity (if your data meets the requirement that all child keys exist in the parent) may help Power BI use more efficient queries and more predictable filter propagation. Also, confirm your storage modes, tables set to DirectQuery or Dual can behave differently, especially when complex filters and slicers are involved. If rows are still not correctly limited, review your relationship direction (single vs. bidirectional) in the model so that the Session table's selection flows down to Joblet and restricts data as expected.
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Assume Referential Integrity setting in Power BI Desktop - Power BI | Microsoft Learn
Table Storage Mode in Power BI Semantic Models - Power BI | Microsoft Learn
Add a filter to a report in Power BI - Power BI | Microsoft Learn
Bidirectional cross-filtering in Power BI Desktop - Power BI | Microsoft Learn
6 Replies
ZhineeAdd some more details:
it worked when I used the built-in relative time filter, Date Search is on Session [start time]When I remove the built-in relative time filter, and used my own filter, it starts to break:
Hours is 4 basically means show the session that is in past 4 hours,(It did filter the session visual but caused error on the joblet visual)
v-hashadapuCommunity Support
Hi Zhinee , Thank you for reaching out to the Microsoft Community Forum.
When no session is selected, your Joblet visual isn’t receiving any filter context, so it queries all rows in the child table. In DirectQuery mode, this can easily exceed the strict row limits enforced by Power BI, resulting in your error. Applying a measure like ISFILTERED on the parent works only if you use its result as a filter on your child visual. If that's not done, the relationship alone doesn't restrict the Joblet rows to zero, so Power BI tries to fetch everything. Using the built-in relative time filter avoids this because it always enforces a filter on the parent, limiting child visual results.
For best results, explicitly add your measure output (such as IsSessionSelected) as a visual-level filter on Joblet, set to 1. Alternatively, use DAX logic that returns BLANK or restricts results in the child visual unless a session is selected. This proactive filtering is required, otherwise, the visual attempts to retrieve every Joblet row, resulting in performance and row limit errors.
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft Learn
- Zhinee
So I noticed something:
When I apply the result of IsSessionSelected = 1 to the Joblet visual, it starts to break and shows the “exceeds 100,000 rows” error.If I remove IsSessionSelected = 1 from the Joblet visual, the error disappears. Instead, it just shows all the Joblet records.
(The “Past Hour” slicer filters the Session visual, but it doesn’t filter the Joblet visual — the Joblet visual still shows all records.)