Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |