Forum Discussion
Helper IIIError - visual as exceeded the available resource
Hi,
I am having a problem where I get this error message saying that "visual as exceeded the available resources. Try filtering the amount of data displayed". I haven't had this happen previously
I don't have much experience working on large datasets. Here is bit of background info on my model . My power bi file is using direct query to an SQL database with about 5 million rows
I have used measures in myvisual to aggregate the data. I have a separate date table in the model. The problem occurs when I try to bring in the quarter-year column from the date table into a table visual which contains a simple Sum (table[amount]) measure
However, when I bring the date from the fact table the error does not occur. Any idea why this is happening and what i can do to fix it ? I have tried limiting the number of rows in my fact table to 100000 rows but doesn't help
Thank you
I managed to fix it I think. I changed the storage mode for my aggregated tables from direct to import. The dimensions are in import mode as well. Once I made the changes the interactions between the various visual elements are really quick
However, the granularity of the table rolled up to a high level. Going down further might cause the same issues again. I will test this further but for now it appears to be working
7 Replies
- PijushRoy
Community Champion
Hi Uniqueusername
Please create a summarize table with Aggregate level. use the summary table for visual
https://medium.com/power-platform/dax-power-bi-summarize-creating-a-single-column-summary-table-84e9c460d1b6#:~:text=Let's%20go%20to%20the%20Modeling,we%20specify%20the%20Orders%20table.If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS
Thanks
Pijush
Uniqueusernamethank you, but how I go about bringing in date fields for the visuals. The date fields I am trying to pull in are from the date table
The problem doesn't seem to occur when I use the date attributes directly from the fact table in my model- Uniqueusername
Hi,
I tried the using aggregated tables as suggested in this link
I tried using DAX to create a caculated table which aggregates the results in an attempt to reduct the number of records. However, I still get the following error "the resultset to the query of an external datasource has exceeded the maximum allowable limit of 1000000"
To counter this, I wrote an sql query that groups the attributes to show the aggregated results. It is an improvement but it is still a bit slow. I am still testing this out as the columns I need might increase and this increasing the number of records
- PijushRoy
Hi Uniqueusername
Set some default data slicer, which means if you have 2 years of data, set default slicer for one month of data
Please share an image for your data model and an image of the visual (hide sensitive data)
If you are using measure, please share - Uniqueusername
An update. I used aggregated tables in sql and managed to reduce the number of rows from 5M plus rows to about 20 thousand rows. I have used direct query for the aggregated table and import query for the dimension tables
The dimension table are categories are single column table that has been imported using the following SQL query
Select Column_name From Table
However, when I add the attribute from the dimension table to the table visual or graph visual it takes a very long time for the report to refresh. The refresh seems to be a lot quicker when I used the corresponding dimension attributes from the fact table instead
I had a look at this blog by Chris Webb
https://blog.crossjoin.co.uk/2020/01/20/visual-has-exceeded-the-available-resources-error-power-bi/For some reason I can't expand on the details of the problem. I don't have "show details" in the dialog box
- Uniqueusername
I managed to fix it I think. I changed the storage mode for my aggregated tables from direct to import. The dimensions are in import mode as well. Once I made the changes the interactions between the various visual elements are really quick
However, the granularity of the table rolled up to a high level. Going down further might cause the same issues again. I will test this further but for now it appears to be working
- Uniqueusername
Looks like I have to revisit this post of mine as I have started encounter performance issues
Firstly, here is a brief explanation of my model when the performance was great - filters were getting applied instantly, cross filtering had no issues. I tried to model my tables as per some of the guidelines and resources I found online ( https://dax.tips/2021/09/06/intro-to-power-bi-aggregations/ )
1) AGG_FACT in import mode
2) Date table in import mode
3)Main_Fact Table in Direct query mode
5) Dimensions in dual mode
Using this setup I had no issues. I played around with DAX studio using the limited knowledge of the tool I have and found that the aggregations tables were being referenced where necessary
Now, in this model, the AGG_FACT table is an SQL source imported by directly writing a query in the data import stage,.i.e, in the Transform Data stage, I connect to the server, specify the database name and write an SQL query to generate the aggregated tables which eventually gets imported into Power BI
Everything was working smoothly until I tried to tweak my approach a bit. The only change I made was writing a query to create a view directly in SQL (SSMS) and then importing that AGG_FACT view into power bi. The query I use to create the view is the same query I wrote previously in the import stage
Is there any reason for the performance to get affected ? I mean it is still essentially the same AGG_Fact table with the only difference being the data being imported is the AGG_Fact view from SQLAny help would be much appreciated
