Hi,
I have a report which has multiple direct queries coming from SQL views into Power BI Desktop (RS Server version). I am using the latest version (May 2020 with June 30th patches)
One of the my direct query SQL views helps populate 2 visuals. There are about 6300 rows of data coming from the data source right now, and it runs very quick in SQL.
The 1st visual (a KPI card) simply provides a count and works fine.
The 2nd visual (a bar chart with 3 drill-downs, 4 fields) does not load and gives the error "Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.. The exception was raised by the IDbCommand interface."
After playing around, I noticed two things:
1. If i reduce the thr drill-down to 2 levels (3 fields), the 2nd visual (bar chart) works
2. Instead of running "Select <column names> from View" if i change the query to "Select top 7000 <column names> from View" the 2nd visual start working (note there are only 6300 rows of data in my testing, so output for both is same)
This seems to be a Power BI aggregation/grouping issue in the bar chart (which has multiple drill-downs so has multiple levels of aggregation).
Is there a setting which I could change? Or what esle can be done to manage this (especially if the number of rows increase in the future)? Would this have to be handled similarily in Power BI Report Server?
