DAX currency conversion at the correct transaction grain

Convert amounts at the grain required by the reporting policy, then aggregate values in the target currency. Multiplying a combined multi-currency total by one average rate generally does not preserve the original transaction meaning.

Define the rate's direction, effective date and unit. “83.5” is incomplete without stating whether it means INR per USD, USD per INR or another convention.

Use the independent currency fixture

Import FactFXSales and DimFX from the extension lab. All exchange rates are invented teaching values, not current or historical market quotes.

F1 and F3 occur January 3; F2 occurs February 3. AmountLocal uses major local-currency units, not cents or paise. The expected combined target amount is ₹29,650.

These files are separate from FactSales, whose amounts use paise. Do not append the two fixtures or assume identical units.

Establish one rate per required key

DimFX has a unique RateKey combining date and currency. Relate DimFX[RateKey] one-to-many to FactFXSales[RateKey]. Preserve numeric rate precision and validate that every transaction has exactly one applicable rate.

This simplified key assumes one approved rate type per date/currency. If the source has several providers, rate types or effective times, include the fields required to select the authoritative rate under the reporting policy.

A unique row index does not resolve two conflicting rates for the same business key. Determine which rate is appropriate before assigning the relationship key.

Convert inside the transaction iterator

Converted INR =
VAR MissingRates =
    COUNTROWS(
        FILTER(
            FactFXSales,
            ISBLANK(RELATED(DimFX[INRPerLocalUnit]))
        )
    )
RETURN
    IF(
        MissingRates > 0,
        BLANK(),
        SUMX(
            FactFXSales,
            FactFXSales[AmountLocal] * RELATED(DimFX[INRPerLocalUnit])
        )
    )

RELATED retrieves the rate through the existing relationship in the iterator's row context. Microsoft documents that requirement in its RELATED reference.

The explicit missing-rate guard prevents a partially converted total from appearing complete. Pair it with a separate missing-rate count or status so readers know why the measure is unavailable.

Do not average rates across incompatible amounts

USD 100 plus USD 200 can be described as USD 300, but the EUR 50 cannot be added to it as though all 350 units share one currency. Even within USD, multiplying 300 by an unweighted average of 83.5 and 84 gives 25,125, while transaction conversion gives 25,150.

The amounts weight the effective rates differently. An average-rate policy can be valid for a specifically defined reporting process, but it must be an intentional policy with appropriate evidence, not an accidental shortcut in a measure.

This tutorial demonstrates arithmetic and model grain, not an accounting-policy recommendation.

Define rounding and missing-date policies

Decide whether amounts are rounded per transaction or only after aggregation, and use the required precision consistently. Those choices can create small differences across large populations.

If a rate is unavailable for a date, do not silently carry forward the previous rate without an approved rule. Record the effective rate date and any substitution so a reviewer can distinguish observed rates from fallback treatment.

Validate positive rates and source coverage separately from the DAX missing-value guard. A nonblank but invalid zero or negative rate should not pass merely because RELATED found a row.

Reconcile each conversion before the total

Check F1=8,350, F2=16,800 and F3=4,500. Then confirm the total 29,650. The extension script uses Decimal arithmetic for these reference checks; it does not execute DAX or establish an actual currency-market dataset.

Exercise: remove F2's rate in a copy. The complete converted total should become unavailable with a visible missing-rate exception, not fall to the sum of F1 and F3. Restore it, then test a conflicting duplicate rate key before allowing the relationship to load.

NeuraPath's Data Analytics with Generative AI course connects DAX iteration with unit and grain discipline. A trustworthy currency measure preserves the transaction's rate policy and makes incomplete conversion visible.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Build an inventory balance from movement transactions.
• Continue with Calculate weighted margin instead of averaging percentages.