Switch for different Measures

Question

I would like to use three currencies for the reporting. These currencies come as columns in the facts. So we have 3 meausures

[Measures].[Amount EUR]
[Measures].[Amount USD]
[Measures].[Amount CHY]

for the Amount in EUR, USD, CHY.

The user can select his currency preferably in the global filter menu. Therefore, we use an additional Dimension [Currency Reporting] which contains the three currencies EUR, USD, CHY, which is not mapped to the Cube. Depending on the selection, the different measures should be used in all the MDX statements of all widgets.

Currently, we use the function currencyCheck to check which element of the currency dimension was selected, and it accordingly chooses the measure.

Is there a standard way of doing this? Do you have any experience with this, and an idea of the best practise way to do it?

iif
(Curr_Member is [Currency Reporting].[Currency Reporting].[Currency Reporting].[EUR],
[Measures].[Amount EUR],
iif(Curr_Member is [Currency Reporting].[Currency Reporting].[Currency Reporting].[USD],
[Measures].[Amount USD],
[Measures].[Amount CHY])
)

CREATE CALCULATED MEMBER [Measures].[Amount] as
currencyCheck([Currency Reporting].[Currency Reporting].CurrentMember )

ic3 ic3 · Accepted Answer · 2020-02-04T13:27:04

It is an option if you have a few measures that are amounts (need fx).

If no another option is using a 'Utility/Statistical dimension' for doing fx calculations. You can inspire yourself from.

Note, if fx rates are time dependent you'll have to apply the fx for each month / day. Here it's not the case as you've the amounts already calculated for the different currencies

Switch for different Measures

1 Answers