FX Rate Connections

I would combine the data table and the data tags table so that there is only 1 table with both the value and the currency. This is likely to make your measures much simpler and more efficient.

I would also suggest reading https://www.daxpatterns.com/currency-conversion/ which goes into quite a bit of detail and has several code snippets which you may be able to adapt to your model.

Hi CM_Mills 

 

Try the following measure:

Converted to FX = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            Dates[Date],
            DataTags[Data Currency],
            "@lc", CALCULATE ( SUM ( 'DataTable'[LC] ) )
        ),
        "@converted",
            DIVIDE (
                [@lc],
                LOOKUPVALUE (
                    FXRates[FX Rate],
                    FXRates[Dates], [Date],
                    FXRates[FX Currency (From)], [Data Currency]
                )
            )
    ),
    [@converted]
)

Please see the attached pbix.

Hi CM_Mills 

In your model, the challenge comes from the fact that your FX table has both date and currency, while your main Data Table has date but the currency information only exists in the separate Data Tags table. Since the Data Tags table doesn’t contain dates, you can’t directly relate it to the FX table. The correct approach is to bridge these relationships: link the Data Table to the Date Table on the Date field, link the FX Table to the Date Table on Dates, and link the Data Table to Data Tags on the Unique Identifier. This way, your Data Table provides both the transaction date and, via the Data Tags, the relevant currency. To pull in the FX rate, you then create a measure that uses both the transaction’s date (from the Date Table) and the currency (from Data Tags) to filter the FX Table—for example, with a CALCULATE that applies TREATAS or a relationship with USERELATIONSHIP. This structure ensures that when you select a data output, Power BI can find the right FX rate by matching both the correct date and the correct currency.