Forum Discussion

EHa's avatar
EHa
Icon for Helper I rankHelper I
5 years ago

Published Report returning "Query Result is too large" error when replacing a column with a measure

Hi All ,

Hoping one of you can spot a possible error with my measure that can help me here.

 

I have several tables across pages in my report. The final page has a dropdown to select a currency and has some of the value columns replaced with a measure that finds the appropriate exchange rate for the selected currency.

This is the measure in question: 

 

Value Converted =
If (
ISCROSSFILTERED(Results_ExchangeRates[Rate]) ,
VAR SelectedCurrency = SelectedValue( Results_ExchangeRates[Code] )
Var FXRatesIDRates = Summarize( Results_ExchangeRates , [FXRateSetID] , [Rate])
Var Result = IF(
Not IsBlank(SelectedCurrency) ,
if (
SelectedCurrency = "USD" ,
Sum('Table'[Value]) ,
SumX( FXRatesIDRates ,
Sum('Table'[Value]) * [Rate])
)
,Sum('Table'[Value])
)
return Result
)
 This measure works just fine in both the pbix file in desktop , and in the published version, but when I try to add currency conversion to my front page I copy /sync the slicer to the first page, and replace the displayed values with the measure ( of course edited to replace the "table[Value]" with the appropraite table/ column combinations) I get an error on one of my tables.
 
One of my table has region / class / column3 / value 1 / value 2 / value 3 / value 4 /column8 as the 8 columns , with all 4 of the value columns converting successfully.
I have another table with columns region / class / value 5 / column4 , with value 5 calculating succesfullt, too.
 
All of these columns are from a single table which is connected to via direct query. Both tables load slowly in the PowerBI Desktop app, but when published the smaller table has a " Couldn't load data for this visual" error message, and the top of the page reads the error message . "The query result is too large. Consider removing unused columns, adding visual filters , or reducing the number of string type columns" . This seems bizzare as the larger table loads fine. When a filter to a particular region / class is applied to the page, the small table appears, with the filter applied, and the converted values calculated.
Need Help

1 Reply