Calling API based on Input Parameters and runtime basis

[Anonymous]

Requirement: the exchange rate is sensitive (assumption) and can not be stored in the Power BI. The need is that it calls API only when some button is calculated.  Do the calculation and return the results in the calculated field.

Step1 : User selects USD on USD/EUR  selector button

Step 2:  API call is made to fetch the rates (INR to USD, PKR to USD, AED to USD, CHF to USD) 

Step 3: Calculated field is calculated runtime (100 * rates fetched above)

Step 4: If User selects, EUR, API call be made to get the  rates and calculate

Note: Any free API site that gives exchange rates can be used .

Appreciate your respone.

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

1. First, obtain a free API key from a currency exchange rate service. You can create a Power Query function that retrieves the rates using the API.

2. Create a Dataflow for Exchange Rates.

3. Get Data from Dataflow.

4. In your main Power BI report, create a measure that calculates the exchange rate based on the selected currencies:

Total Amount Selected Currency = 
            CALCULATE(
                SUMX(
                    VALUES('Currency Table'[Currency Code]),
                    SUM('Finance Data'[Amount in EUR]) * VALUES('Currency Table'[Conversion Rate])
                )
            )

For more details, please refer: 

Currency Exchange Rate Converter Power BI Function - Part 1 - RADACAD

Currency Exchange Rate Conversion in Power BI with Live Rates - Part 2 - RADACAD

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Hello!

Thanks for replying. However, In this approach, the currency rates are stored in power BI. Kindly note that in my case, the exchange rate is to be considered sensitive and not stored in PBI at all.

[Anonymous]

Hi @Anonymous ,

When you explicitly toggle the enhanced compute engine to On in the dataflow settings, you can use Direct Query connection mode to connect to the dataflow. 

In direct query mode, the exchange rate is not displayed in the data view.

For more details, please refer: Using DirectQuery with dataflows - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.