Hi All,
I have the following issue.
TableA: "Table P&L" and Table B: "Fx Rate" are linked by relationship by Currency
in the Matrix I want to compare by Staff vs Time Period for example. and restated the amounts into EURO and I want to have a dynamic choice in the slicer to let the user pick which period of FX rate to translate into EURO. ( I don't want to increase the number of rows in my background queries because I have so many rows and it will become very large and slow.)
2018 2019 2020
Staff
rent
Total
e.g. Table A (P&L Table)
Country Month Year CCY Tx Currency Amount Cost Type
Japan 10 2020 JPY 17591 Staff
Japan 11 2019 JPY 165426 Rent
Germany 10 2020 EUR 264 Staff
UK 11 2019 GBP 235 Rent
UK 11 2020 GBP 232 Rent
China 10 2020 CNY 195235 Staff
FX Rate Table (with the amount translated to Euro)
Month Year FX (CCY/EUR) CCY
10 2019 1.13 USD
11 2019 (I dont want to type the details here ..)
12 2019
10 2020
11 2020
12 2020
I have the following measure written:
Last Year =
CALCULATE(SUMX('P&L','P&L'[Transaction currency amount]/RELATED('Fx Rate'[FX])), FILTER(ALL(........
bascially i filter by month and year at the end which is not very important.
The formula works fine when I tried to filter the FX rate table in the query to force the relationship to become a one-to-many relationship with the P&L table. However, the related function of course does not work when the relationship is many to many.
In short, my question is, is there a way to make the related () function works?? My aim was to make the fx rate one to many relationship with the help of the slicer when the user picks any one value of month..
thanks a lot!
