Native Query brings all data in tabular visual when currency conversion is applied.

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!

 

Hello, Rdarshana ,

I don't like the Many To Many, especially if it's set up to both (direction),

I would consider changing the Rate Table and generate some surrogate key (SK), so you can join your fact table with One To Many (exchance -> fatct).

 

Perhaps from the date range, you can create row for each date and add a valid currency to it and create the SK from it, same way then in your fact table. Perhaps that may help.

Hi Rdarshana,

I would also take a moment to thank grazitti_sapna, vojtechsima  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you

Harshitha.