Forum Discussion
BQ - Direct Query 1 million row limit
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.
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.
Using IF.EAGER solves the issue. Power BI sends a single SQL query, but it includes both (true/false) measure logic in the query. This resolves the 1 million row issue. From Performance point, I don't see any difference.
I have a question: if we have a dimension table with more than 1 million rows, what is the correct approach to handle this in DirectQuery mode?
Glad IF.EAGER worked, I've never had occasion to use it before.
I have to say that I've never worked with so many rows in a dimension before, and my experience with DQ is pretty limited, but I can give you my thoughts for what they're worth.
I don't see how you could produce a meaningful report with so many unique values, so I would try to reduce the number of rows to manageable proportions. There are a couple of different approaches I can think of. The first step would be to identify the columns that you actually want to be able to slice and dice by, which would hopefully have a much smaller number of unique combinations.
One option would then be to create a snowflake schema, with one or more tables containing the unique combinations of columns you need linking one-to-many to the dimension table.
Another option would be to rework the dimension table itself so that it only had the columns you were interested in. This would likely require creating a new key in both the dimension table and the fact table for use in the relationship.
I think the second option would be my preferred method, but given the amount of data you're talking about it might be prohibitively expensive in terms of compute resources.