Multiple currencies create circular reference

[#1]

Hi All,

I was wondering if anyone could help me.

I have a data model with 2 main tables

Location and Policy as shown in the sample data model below

in these I have 2 fields where is stored the currency locationPremiumCurrency and policyPremiumCUrrency.

The filed Currency target is the currency that the user wants to view the data. meaning that no matter the currency in the record, when the user selects USD, all the values will be displayed as USD. So this column (CurrencyTarget will be a filter object)

How can I remove the circular reference keeping the unique column?

Could I separate the tables and build a macro to connect the 2?

Thanks in advance for your help.

[#2 · Accepted solution]

Hi All,

I managed to do it with set analysis.

Sum({<PolicyCurrencyTarget=LocationcurrencyTarget>} PolicyPremium *PolicyExchangeRate)

this way i keep the exchange rates separated and I can link them in the measure