Forum Discussion
BQ - Direct Query 1 million row limit
Power BI DirectQuery + BigQuery – Dynamic Measure causing 1M row limit error
I have a Star Schema model with the following setup:
1 Fact table with ~10 billion rows
4 Dimension tables, each with ~1+ million rows
All tables are stored in BigQuery
Power BI connects using DirectQuery
Measures
Measure1 = SUM(Fact[cost]) Measure2 = SUM(Fact[cost_2])
I need users to switch between these measures using a slicer.
To implement this, I created a static table in Power BI (no relationships with other tables):
fieldCost_1 This table is used in a slicer.
Dynamic measure:
Measure3 = IF( SELECTEDVALUE(NewTable[field]) = "Cost", [Measure1], [Measure2] )Visual
I created a Table visual witth
- DimTable[field1]
- [Measure1]
Filtered by Year = 2023
This works fine.
The SQL query generated is similar to:
SELECT dim.field1, SUM(cost) FROM fact f JOIN dim ON ... WHERE year = 2023 GROUP BY dim.field1Even though the dimension table has 1M+ rows, only a subset has spend in 2023, so the visual loads correctly.
Problem
When I replace [Measure1] with [Measure3] in the visual:
The visual fails with "1 Million row limit" error.
After checking the queries sent to BigQuery, Power BI sends two queries:
1️⃣ Query 1
Fetches distinct dim.field1 from the dimension table
(This exceeds the 1M DirectQuery row limit and fails)2️⃣ Query 2
The expected aggregation query.Additional Observation
If no value is selected in the slicer, the visual works.
As soon as I select a value in the slicer, Power BI sends the extra distinct dimension query.
Model Note
DimTable has a many-to-many relationship with the fact table.
Question
Why does Power BI generate a distinct query on the dimension table when the dynamic IF + SELECTEDVALUE measure is used?
Is this behavior caused by:
The disconnected slicer table
The IF / SELECTEDVALUE logic
The many-to-many relationship
Or DirectQuery limitations
And what would be the recommended approach to implement dynamic measure switching in DirectQuery models with very large dim tables?
The only other thing I could think to try is to force the calculation of both measures in the hope that that will enable Power BI to pass the filter to BigQuery like it does when you call a simple measure.
You could either use IF.EAGER or rewrite the code using variables like
Measure3 = VAR Measure1 = [Measure1] VAR Measure2 = [Measure2] RETURN IF ( SELECTEDVALUE ( NewTable[field] ) = "Cost", Measure1, Measure2 )Performance isn't going to be good, but then I would think that performance will already be so slow that you may not notice the difference.
6 Replies
You could try using a field parameter instead of the manual switcher that you have created. The functionality is basically the same, but as it is built in to Power BI you won't have the additional overhead, and it should function exactly the same as if you had called the base measure instead.
I can't use a field parameter because [Measure3] is used by multiple measures (10).
If I use a field parameter, I can change [Measure1] and [Measure2] dynamically in the visual. However, this would require me to duplicate the 10 measures, resulting in 20 measures in total. I feel this is redundant. That’s why I didn’t prefer using field parameters and bookmarks.The only other thing I could think to try is to force the calculation of both measures in the hope that that will enable Power BI to pass the filter to BigQuery like it does when you call a simple measure.
You could either use IF.EAGER or rewrite the code using variables like
Measure3 = VAR Measure1 = [Measure1] VAR Measure2 = [Measure2] RETURN IF ( SELECTEDVALUE ( NewTable[field] ) = "Cost", Measure1, Measure2 )Performance isn't going to be good, but then I would think that performance will already be so slow that you may not notice the difference.
