How to create and use User Defined Function for Currency Conversion in Power BI

How to apply the Currency Conversion in Power BI using User Defined Function
Scenario:

Imagine you are a global retail company analyzing sales across multiple countries. Your sales data is stored in a fact table (fct_Orders_Sample) with gross sales values in the base currency (USD). Business users want to view Gross Revenue in their local currency, either based on the country they select in a report or by explicitly passing a currency code (like GBP or EUR).

In Power BI, a User Defined Function (UDF) is a modular block of DAX logic that you can parameterize and reuse across multiple measures. Instead of repeating the same calculation in different places, you encapsulate it into a function and pass arguments to control its behavior.

We can achieve the above Scenario as explained in below Methods.

Data Model:

Relationships:

Notes:

ref_Currency_Mapping: This is a Currency Rate mapping table which is disconnected from the Data Model and will be used as Lookup table for the sample of Currency Conversion Rates.

ref_Currency_Mapping =

DATATABLE(

    "Currency_ID", INTEGER,

    "Country_ID", INTEGER,

    "Country_Name", STRING,

    "Currency_Code", STRING,

    "Conversion_Rate", DOUBLE,

    {

        {101, 234, "India",         "INR", 92.40},   -- 1 USD ≈ 92.4 INR

        {102, 235, "Japan",         "JPY", 157.00},  -- 1 USD ≈ 157 JPY

        {103, 236, "United States", "USD", 1.00},    -- Base Currency

        {104, 237, "Canada",        "CAD", 1.37},    -- 1 USD ≈ 1.37 CAD

        {105, 238, "United Kingdom","GBP", 0.75},    -- 1 USD ≈ 0.75 GBP

        {106, 239, "Spain",         "EUR", 0.85}     -- 1 USD ≈ 0.85 EUR

    }

)

Base Measure:

Gross Revenue = Sum(fct_Orders_Sample[Gross_Sales])

Note: The Default Currency for [Gross Revenue] is USD when there is no specific Currency selected.

Now we can do the Currency Conversion based on the above mapping table, using the below methods.

Method 1: Conversion Based on Selected Country:
In this Method, based on the selected Country from Slicer of a dim_Country[Country], we will apply the Conversion rate.

Selected Currency Conversion Rate =

VAR _SelCurCode= SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR _Conversion_Rate =

    LOOKUPVALUE(

        ref_Currency_Mapping[Conversion_Rate],

        ref_Currency_Mapping[Currency_Code], _SelCurCode

    )

RETURN

IF(ISBLANK(_Conversion_Rate), 1, _Conversion_Rate)

Gross Revenue (Selected Currency) =

[Gross Revenue] * [Selected Currency Conversion Rate]

We have used the below function for Dynamic Format for above measure.

fx_CurrencyFormat(BLANK())

The definition of the Function for Dynamic Currency Format:

fx_CurrencyFormat = ( CurrencyCode: string val) =>

VAR _Selected_Currency = SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR CurrencyCode = COALESCE(CurrencyCode, _Selected_Currency)

RETURN

    SWITCH(

        CurrencyCode,

        "INR", "₹ #,0.00",       -- Indian Rupee

        "JPY", "¥ #,0",          -- Japanese Yen

        "USD", "$ #,0.00",       -- US Dollar

        "CAD", "$ #,0.00",       -- Canadian Dollar

        "GBP", "£ #,0.00",       -- Pound Sterling (UK/England)

        "EUR", "€ #,0.00",       -- Euro (Spain, Eurozone)

        "$ #,0.00"               -- Default USD

    )

Result:

Method 2: Conversion Using a User Defined Function:
In this method, we will use a User Defined Function with a Currency Code Parameter, enables us to explicitly pass a specific Currency Code (e.g., always show GBP regardless of selected country).

In Power BI, we can create a User Defined Function from Model view as shown below.

The below Function is a Model dependent, which depends on the ref_Currency_Mapping table from the Data Model.

fx_CurrencyConversion = ( CurrencyCode: string val) =>

VAR _Selected_Currency = SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR _Currency_Passed = COALESCE(CurrencyCode, _Selected_Currency)

VAR _Conversion_Rate =

    LOOKUPVALUE(

        ref_Currency_Mapping[Conversion_Rate],

        ref_Currency_Mapping[Currency_Code], _Currency_Passed

    )

RETURN

IF(ISBLANK(_Conversion_Rate), 1, _Conversion_Rate)

Gross Revenue (Selected Currency in fx) = [Gross Revenue] * fx_CurrencyConversion("GBP")

The below Function is used for the Dynamic format string:

fx_CurrencyFormat("GBP")

Result:

Note: 

You can customize this User Defined Function to suit your own scenario. Whether you are working with different currencies, adjusting for tax rates, or applying other business rules.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------