Dynamic Report: Two Approaches

For picking one of several pre calculated currency measures from a slicer, field parameters are the better fit than wrapping measures in SWITCH with SELECTEDVALUE. With field parameters the engine substitutes the chosen measure directly at query time, so only that one measure evaluates. SWITCH does short circuit unused branches, so on a simple model the gap is small, but field parameters avoid the extra SELECTEDVALUE and branching layer and are much cleaner to maintain.

 

The pattern that scales much better than either of these is a Currency dimension holding exchange rates against a base currency, a currency slicer tied to that dimension, and one sales measure per category that multiplies base currency sales by the selected rate (CALCULATE with the rate measure, or LOOKUPVALUE on the selected currency). You end up with one measure per category instead of four, and adding a new currency becomes a data refresh rather than a model change.

 

To compare the two approaches on your actual file, run Performance Analyzer, switch currencies, and record the DAX query durations for each visual. That will show the real difference on your model.

 

If this helped, a thumbs up and accepting the solution would be appreciated.

 

Best,

Shai Karmani

 

Let's connect in LinkedIn

Hi powerbiexpert22​ 

Both approaches can work, but I would not choose between them based mainly on performance.

A SWITCH based on a simple slicer selection can be very efficient, because Power BI can normally evaluate only the selected branch. Field Parameters are primarily intended to dynamically change the fields or measures used by a visual; they are not specifically a performance optimization feature.

The bigger issue in this example is the number of measures being created.

If Beverages, Electronics and Furniture are values of a Category column, you normally do not need separate measures for each category and currency combination.

For example, you can keep a single base measure:

Sales = SUM ( Sales[Amount] )

Use Category directly in the visual, and create only one currency-conversion measure based on the selected currency.

This would reduce the number of measures considerably and make the model easier to maintain.

If the exchange rates are date-dependent, I would recommend using a proper Exchange Rate table. If currency conversion needs to be applied to several financial measures, a Calculation Group is also a very good option, because the same conversion logic can then be reused across measures.

So, in this case I would consider:

1. Base Sales measure
2. Category as a dimension in the visual
3. Currency table / exchange-rate table
4. One currency conversion calculation, or a Calculation Group if it needs to be reusable

Field Parameters are useful when the requirement is to let the user dynamically change which measure or dimension appears in the visual, rather than simply to perform currency conversion.

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

Hi powerbiexpert22​ 

We would like to inquire whether have you got the chance to check the solutions provided by other users in community to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Hi powerbiexpert22​ 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Thank you.