Was using this measure to calculate revenue amount in a specified reporting currenty by looking up an exchange rate table
with the method outlined in this link https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-us...
NetAmount Sum Ex = sumx(vwFactRevenueReportingEOM,
vwFactRevenueReportingEOM[NetAmount]*
IF(HASONEVALUE(DimReportCurrency[CurrencyID]),
LOOKUPVALUE(vwDimExchangeRate[ExchangeRate], vwDimExchangeRate[DateID], vwFactRevenueReportingEOM[ExchangeDateID], vwDimExchangeRate[SourceCurrencyID],
vwFactRevenueReportingEOM[CurrencyID], vwDimExchangeRate[ReportingCurrencyID], values(DimReportCurrency[CurrencyID]))
, 1)
)
The measure works well in SSAS tabular model. But when I tried to use it in Power BI (direct query mode), it throws out the error of The resultset of a query to external data source has exceeded the maximum allowed size of '1000000'.
The vwFact table and the exchange rate table have more than 1 million rows. But I have set filters to the report so that only a dozen records will be returning.
Anyone got similar issues? I tested and the issue is with the Lookupvalue function used. Is there any workarounds EG not using the lookupvalue function?
Thanks,
