Error - visual as exceeded the available resource

[Uniqueusername]

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

[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 SQL

Any help would be much appreciated

[Uniqueusername · Accepted solution]

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]

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

[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

[PijushRoy]

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-84e9....

If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS

Thanks
Pijush

[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

[Uniqueusername]

thank 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