Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Index Match - Match row datas with table header

I'm looking for similar function like Index Match- Refer below table. From "Currency" table- I wish to bring exchange rate in "data" table- by matching date and Currency with currency table date and ...
  • Anonymous's avatar
    Anonymous
    7 years ago

    Currency Table unpivot no relation now

     

    Thanks for help. As suggested by you, I have unpivot the table as below: But unable to write the lookup formulae- As I need to match date as well as currency before picking up the value. Could you help me with the formulae.

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anyone else know any medoth, where the formulae ready amd match curreny in rows with table headers.

parry2k wrote:

Anonymousset relationship between currency and data table on date, where currency table will be on one side of the relationship.

 

Add following column:

 

FX Rate = 
SWITCH(
  DataTable[Currency],
  "OMR", RELATED( CurrencyTable[OMR] ),
  "USD", RELATED( CurrencyTable[USD] ),
  "EURO", RELATED( CurrencyTable[EURO] )
)

This is one way to do this, other way would be to unpivot currency table and then use lookup.

 

 

parry2k wrote:

Anonymousset relationship between currency and data table on date, where currency table will be on one side of the relationship.

 

Add following column:

 

FX Rate = 
SWITCH(
  DataTable[Currency],
  "OMR", RELATED( CurrencyTable[OMR] ),
  "USD", RELATED( CurrencyTable[USD] ),
  "EURO", RELATED( CurrencyTable[EURO] )
)

This is one way to do this, other way would be to unpivot currency table and then use lookup.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello, how to write measures for above two currency table options?

Will they be more effective or effiecient and consume less computing power?  In my data-what happens every time new data or new data table comes and the currency exchange rate has to be applied. So wish too know out of the four options- i.e. with currency table- calculated column or measure  ,  pivot currency table- calculated column or measure- which one will be the effienct way