topic Re: Use Column Value to choose which column of another table to lookup in DAX Commands and Tips

Use Column Value to choose which column of another table to lookup

GFRASER — Fri, 03 May 2024 15:04:46 GMT

Hello

I am trying to get EXCHRATE in the first table to lookup an FX table that has the various periods in the rows and the columsn are the different currencies.

I want the lookup rather than using "EUR" which is currently hardcoded in the formula to use the value from Column2 in the first visual/table.

Potentially a better way of getting the FX rates but they are on this table currently so was trying to pull from there without much luck?

If you're feeling generous where the Period number is hardcoded as 9 currently ideally want that pulling from a slicer selection if there's a way of doing that?

All help gratefully appreciated.

Thanks

Re: Use Column Value to choose which column of another table to lookup

lbendlin — Sat, 04 May 2024 00:16:43 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Use Column Value to choose which column of another table to lookup

GFRASER — Tue, 07 May 2024 08:31:46 GMT

Thanks for the advice. I am trying to find a way to lookup the EXCHRATE value in the table below from the FX table. I need it to look up the CURRENCY field in the FX table. If I can get it to pick the Period based on a slicer or something that would be good, otherwise I can filter the FX table with a global slicer so only the period required is showing in the table.

If for example purposes we say the period is 7 then I'd expect the Currencies with EUR to return 1.1567, CAD 1.6878, CZK 28.3698 in the EXCHRATE field.

Thanks

DATAAREA	CURRENCY	EXCHRATE
AUT	EUR	1.1682
BEL	EUR	1.1682
CAN	CAD	1.1682
CZE	CZK	1.1682
EUT	EUR	1.1682
GER	EUR	1.1682
HOL	EUR	1.1682
IRL	EUR	1.1682
LIV	GBP	1.1682
NEV	GBP	1.1682
PO1	PLN	1.1682
PO2	PLN	1.1682
POL	PLN	1.1682
SVK	EUR	1.1682
TDP	PLN	1.1682
TEL	GBP	1.1682
TIL	GBP	1.1682
TLL	GBP	1.1682
TOL	GBP	1.1682
TRP	GBP	1.1682
TSL	GBP	1.1682
USA	USD	1.1682

FX LOOKUP TABLE

Period	EUR	CAD	CZK	PLN
7	1.1567	1.6878	28.3698	5.017
Average	1.161373	1.699082	28.56907	5.1249
8	1.1665	1.7165	28.8786	5.128
9	1.1682	1.7064	29.6576	5.0385

Re: Use Column Value to choose which column of another table to lookup

Anonymous — Tue, 07 May 2024 09:27:19 GMT

lbendlin Thanks for your contribution on this thread.

Hi GFRASER ,

You can follow the steps below to get the expected result, please find the details in the attachment.

1. Unpivot these columns of table 'FX' except the column 'Period' in Power Query Editor

= Table.Unpivot(#"Changed Type", {"EUR", "CAD", "CZK", "PLN"}, "Currency", "Value")

2. Create a slicer and apply the field [Period] of table 'FX'

3. Create a measure as below to get the exchange rate base on different period and currency

EXCHRATE = VAR _period = SELECTEDVALUE ( 'FX'[Period] ) VAR _currency = SELECTEDVALUE ( 'Table'[CURRENCY] ) RETURN CALCULATE ( MAX ( 'FX'[Value] ), FILTER ( 'FX', 'FX'[Period] = _period && 'FX'[Currency] = _currency ) )

Best Regards

Re: Use Column Value to choose which column of another table to lookup

GFRASER — Tue, 07 May 2024 12:18:24 GMT

Thanks Rena

I've got that working now.

Can I ask how I would use the output of this measure?

I have the table below where it has the DATAAREA in this case BEL, which per the Table called 'Table' in this scenario we know uses currency EUR. I am tring to create a New Column in the table below called GBPCURRENCYAMOUNT which takes the field ACCOUNTINGCURRENCYAMOUNT and divides it by the exchange rate picked up in EXCHRATE.

It is calculating in this example but when I change the slicer associated with EXCHRATE it doesn't update this amount for the different exchange rate. Not sure how I get it to dynamically link to EXCHRATE?

Thanks

DATAAREA	Nominal	Sum of ACCOUNTINGCURRENCYAMOUNT	Sum of GBPCURRENCYAMOUNT
BEL	11200	68,710.32	59,120.91
BEL	11230	- 68,710.32	- 59,120.91
BEL	11300	44,075.80	37,924.45
BEL	11330	- 44,075.80	- 37,924.45
BEL	11400	1,302.15	1,120.42
BEL	11430	- 1,302.15	- 1,120.42
BEL	11500	-	-
BEL	11530	-	-
BEL	11700	-	-
BEL	11730	-	-
BEL	13100	78,305.88	67,195.18
BEL	13110	- 1,377.00	- 1,184.82
BEL	14100	22,056.56	18,978.28
BEL	14300	- 147,319.85	- 126,853.26