topic Re: Index Match - Match row datas with table header in DAX Commands and Tips

Index Match - Match row datas with table header

Anonymous — Tue, 28 May 2019 15:55:25 GMT

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 column header- withought pivoting the Currency Table ( As my currency table has 10 years date data)

Excel Equivalent"

=INDEX($A$3:$D$10,MATCH(G4,$A$3:$A$10,0),MATCH(I4,$A$3:$D$3,0))

Thank in advance for your help.

Re: Index Match - Match row datas with table header

parry2k — Tue, 28 May 2019 16:18:26 GMT

Anonymous set 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.

Re: Index Match - Match row datas with table header

Anonymous — Tue, 28 May 2019 16:42:26 GMT

Thanks. Great. It helps. Is there any other way that it reads the Currency abreviations like OMR, USD etc and match it. As my table has several currencies and more than 3650 dates.

Re: Index Match - Match row datas with table header

parry2k — Tue, 28 May 2019 16:45:56 GMT

Anonymous in thst case you have to unpivot your currency table, to do so, go to query editor, select date column in currency table, right click, and select unpivot other columns. You will get two columns attribute and value, rename those to currency and rate.

Once these changes are applied, we don't need to set relationship between currency and sales table but use lookupvalue function, read here about this function.

Re: Index Match - Match row datas with table header

Anonymous — Wed, 29 May 2019 11:19:53 GMT

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.

Re: Index Match - Match row datas with table header

parry2k — Wed, 29 May 2019 12:44:07 GMT

Anonymous here it is, add a column in data table

FX Rate  =
LOOKUPVALUE
( 
CurrencyTable[Value], 
CurrencyTable[Date],
DataTable[Date],
CurrencyTable[Currency],
DataTable[Currency]
)

Re: Index Match - Match row datas with table header

Anonymous — Thu, 30 May 2019 10:57:42 GMT

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.

Re: Index Match - Match row datas with table header

Anonymous — Thu, 30 May 2019 10:58:19 GMT

Thanks a lot.

Re: Index Match - Match row datas with table header

Anonymous — Thu, 30 May 2019 12:16:46 GMT

Out of the two method, which method is best and consumes less RAM / more efficient. Thanks

Re: Index Match - Match row datas with table header

Anonymous — Fri, 31 May 2019 10:01:29 GMT

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