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.
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.
Thank you for the detailed information. I would also prefer the second option, but the issue I’m facing is that I can’t change the fact table grain. For example, the fact table is at the item ID level, whereas in the dimension table, one item ID can have multiple ABC IDs, resulting in a many-to-many relationship. A snowflake schema may help address the modeling challenge. I’ll try to fix the data model in the most efficient way.