Forum Discussion
Native Query brings all data in tabular visual when currency conversion is applied.
All,
I wanted to inform that the issue was resolved.
A Date bridge table containing column with the first of month was added.
This date bridge table was joined to the Exchange Rate Table via a many-to-one join.
The date bridge table is then joined to the fact table via a one to many join.
The DAX query was then updated accordingly, and this resolved the issue.
Hi Rdarshana,
Issue is with The currency conversion logic:
VAR FilteredRates =
FILTER(
'monthly_average_exchange_rate',
...
)
VAR ConversionRate =
MAXX(FilteredRates, 'monthly_average_exchange_rate'[Rate])
doesn’t evaluate per date row but is re-evaluated per row in your visual, and because you’ve added the ID field, that evaluation is multiplied dramatically.
Even worse, your model involves:
-
A many-to-many relationship (between Fact and exchange rate table on
Exchange Rate Date) -
Filtering using
SELECTEDVALUE()on potentially non-unique combinations (To_Currency,From_Currency,Rate Date) -
A complex, dynamic filter (
>=,<=) for each record
This forces row context → filter context conversion per row in the table, generating large native queries.
You can try below methods
Refactor the conversion logic using TREATAS, which lets you push filters from calculated values more cleanly:
Converted Measure =
VAR ConversionStartDate = MIN('Calendar'[Date])
VAR ConversionEndDate = MAX('Calendar'[Date])
VAR TargetCurrencyCode = SELECTEDVALUE('To_Currency'[Currency Code])
VAR SourceCurrency = SELECTEDVALUE('Fact Table'[Currency Code])
VAR RateDates =
ADDCOLUMNS(
CALENDAR(
EOMONTH(ConversionStartDate, -1) + 1,
EOMONTH(ConversionEndDate, -1) + 1
),
"FromCurrency", SourceCurrency,
"ToCurrency", TargetCurrencyCode
)
VAR FilteredRates =
CALCULATETABLE(
'monthly_average_exchange_rate',
TREATAS(RateDates,
'monthly_average_exchange_rate'[Rate Date],
'monthly_average_exchange_rate'[From Currency],
'monthly_average_exchange_rate'[To Currency]
)
)
VAR ConversionRate = MAXX(FilteredRates, 'monthly_average_exchange_rate'[Rate])
RETURN
IF(
TargetCurrencyCode = "USD",
SUM('Fact Table'[Measure1]),
IF(
NOT ISBLANK(ConversionRate),
SUM('Fact Table'[Measure1]) * ConversionRate,
BLANK()
)
)
This ensures rate filtering happens in bulk, not row by row.
you can try below as well,
Instead of applying exchange rate per record, try:
-
Pre-aggregating measure per month
-
Then doing conversion using a monthly average exchange rate
This drastically reduces the calculation context:
Converted Measure (Monthly) =
SUMX(
VALUES('Calendar'[MonthYear]), -- or RateDate
VAR MonthlyTotal =
CALCULATE(SUM('Fact Table'[Measure1]))
VAR Rate =
CALCULATE(
MAX('monthly_average_exchange_rate'[Rate]),
FILTER('monthly_average_exchange_rate', ...)
)
RETURN MonthlyTotal * Rate
)
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
grazitti_sapna
I tested the two measures. The currency conversion logic is working as such.
But, when I add an ID (lowest level of granularity of the table), I see that the power bi sends the same error in the table visual -
Error fetching data for this visual. The resultset of a query to external dataset has exceeeded the maximum allowed size of 1million rows.
So, now my question is -
if we keep the monthly conversion logic as is, then do you think it would make sense to break the many-to-many join between the fact table and the monthly exchange rate table?Update - I broke the many to many join between the monthly exchnage rate and the fact table by including two bridge tables. So now the joins are all one-to-many.
But inspite of the making this change, and adding the conversion logic for monthly aggregation, I am still seeing the 1 million records issue with the table visual when the ID column is included in.HI Rdarshana,
Power BI's external dataset query limit = 1 million rows
When you use DirectQuery, and include the ID (lowest granularity) in your visual, Power BI generates a query that returns one row per ID per date (or per measure). This can easily exceed 1 million rows, especially if:
-
You have many IDs (e.g., transactions, invoice lines, etc.)
-
You combine that with date or currency dimensions
-
Even if you're aggregating monthly, including
IDin the visual disables pre-aggregation optimization
Below are few suggestions
1. Avoid Using
IDin Visuals Unless Necessary-
The root of the 1M row overflow is not in the measure logic anymore — it's the visual trying to retrieve too many rows.
-
Try to summarize the data before adding
IDto the visual. -
If you must show
ID, implement pagination, filters, or drill-through.
2. Use Aggregated Tables
If your model allows it, materialize pre-aggregated tables (e.g., monthly totals per currency per ID or product), then use those tables in visuals:
-
Create a new aggregated table in Power BI using DAX or in the source DB:
AggregatedFact =
SUMMARIZE(
'Fact Table',
'Fact Table'[ID],
'Calendar'[Month],
'Fact Table'[Currency Code],
"TotalMeasure", SUM('Fact Table'[Measure1])
)-
Then, apply currency conversion on top of this pre-aggregated table.
3. Use Drill-through Instead of One Big Table
Rather than allowing a flat table at full granularity, design your report as:
-
Overview page: summary by month or product
-
Drill-through page: show
ID-level detail only when context is selected
-