Manage grouping (Internal error: An expression services limit has been reached...)
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?
Unable to display the sum of measure
The above image is just taken for reference but I want it to be seen as a visual table in Power BI I have a common company key from table1 and table2. I have subtracted the values of table1 and table2 and created a measure(difference). But there are at times table2 > table1, this picks up a negative value. In the end the total under "difference" is 100, but I want it to be the sum of (100 +200) which I am unable to do. I tried using ABS DAX function measure = ABS([Difference]), which does not affect the total since it takes the difference of 1250-1150 = 100. I tried using an if statement as well : IF( table2 > table1 , difference *-1, difference*1). since the total is not calculated in a vertical fashion, it takes the horizontal difference. Can anyone tell me how to get 300 here?
