Couldn't load the data for this visual

[GJ217]

Hi,

I have created a table visual with the Month & Year and Headcount  and a line graph with the same, I have also adde a Fiscal Year slicer  (FY period being Apr - Mar).

When I select FY23 the visulas load fine but when I select FY21 or FY22 the visuals come up with the Couldn't load the data for this visual with the following error message:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.. The exception was raised by the IDbCommand interface.

Any help with this is much appreciated.

[TomMartens · Accepted solution]

Hey @GJ217 ,

my assumption is that you are using a measure with your visuals that is responsible for retrieving too much data.

Check if the data can be loaded when you remove measures one by one, to identify the measure(s) that is/are responsible.

Consider to post the measure code when identified. Even better create a pbix that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Regards,

Tom

[GJ217 · Accepted solution]

Hi All,

I managed to resolve this by myself in the end. It transpired that the following DAX measures that I used did not include FILTER;

Hadcount2 = CALCULATE(DISTNCTCOUNT(Person[PersonNumber]), Person[HireDate] <=(Dates[Date])

&&(Person[TerminationDate] >= MAX(Dates[Date] || ISBLANK(Person[TerminationDate]))

and I replaced it with the following measure which now loads my visuals;

Headcount1 = CALCULATE(COUNTROWS('Person'),

FILTER(VALUES(Person[HireDate]), Person[HireDate]), Person[HireDate] <=MAX(Dates[Date])),

FILTER(VALUES(Person[TerminationDate]), OR(Person[TerminationDate] >=MAX(Dates[Dates], ISBLANK(pERSON[tERMINATIONdATE]))))

Thank you to those who responded.

[m3tr01d]

helloe @GJ217 

1) Are you using Direct Query inside your data model?
2) Are you using the 32bit version of Power BI?
3) How many RAM do you have on your computer?

Thanks

[TomMartens · Accepted solution]

Hey @GJ217 ,

my assumption is that you are using a measure with your visuals that is responsible for retrieving too much data.

Check if the data can be loaded when you remove measures one by one, to identify the measure(s) that is/are responsible.

Consider to post the measure code when identified. Even better create a pbix that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Regards,

Tom

[speedramps]

Try there training videos

Video 1 

Video 2 

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volnteer solver will get the kudos they deserve. Thank you !