Select specific row and column

[Mark_123]

Hi,

Is there a way to select a specific column and row based on user input from another form?

I'm using this table for the values: 

There is a date column, and then each currency has their own column - which is the exchange rate to GBP for that date.

I want to be able to select a date and the currency, and then the information is filtered and selected for me. I want to pull in a value into 'Exchange rate to GBP' based on the 'Date' and 'Code' field.

I've tried using this formula, but it doesn't work: 

decimal(select(30 days history[Clean Date],AND([_thisrow].[Date]=[Clean Date], [_THISROW].[Code]=[_THISROW].[Code])))

Thank you!

[WillowMobileSys]

The SELECT() function returns a LIST of values - even if only a single value is returned, it is a LIST of 1.  You cannot apply the DECIMAL() function to a LIST.  The value needs to be extracted first.

If you KNOW the expression will return just a single value, then you can extract it from the LIST by using using the ANY() function -- like this:

ANY(select(30 days history[Clean Date],
           AND([_thisrow].[Date]=[Clean Date], 
               [_THISROW].[Code]=[_THISROW].[Code])
    )
)

NOTE:  The ANY() function simply grabs the first value in the LIST.  If there happens to be more than one, only the first value is returned.
NOTE2:  If the value returned, [Clean Date], is of the type you expect, there is no need for any type casting type functions.

I hope this helps!!!

[Mark_123]

Hi Willow,

Thank you for your reply!

The formula you provided doesn't work for me because it is getting a value from "the list of values of column 'Clean Date' ":

- so it is returning a date:

I need it to return a specific decimal value.

For example, in the form, if I set the 'Date' field to "24/06/2024" and the 'Code' field to "EUR", then it should return the value I've highlighted here: 

Sorry if this is a bit confusing or complex!

[Mark_123]

Anyone else able to help with this please? @Suvrutt_Gurjar @AleksiAlkio 

[AleksiAlkio]

Use something like ths and fit it to your needs.

SWITCH([Code],
"EUR",LOOKUP([_THISROW].[Date],"30 Days history","Clean Date","EUR"),
"USD",LOOKUP([_THISROW].[Date],"30 Days history","Clean Date","USD"),
LOOKUP([_THISROW].[Date],"30 Days history","Clean Date","ILS")
)

[Mark_123]

Hi Aleksi,

Thank you for your reply!

This does seem to be working however, I was wondering if there was a formula that doesn't hardcode the values?

eg, something like the below formula which @WillowMobileSys originally suggested:

ANY(select(30 days history[Clean Date],
           AND([_thisrow].[Date]=[Clean Date], 
               [_THISROW].[Code]=[_THISROW].[Code])
    )
)

Thanks in advance!

[WillowMobileSys]

First, note I was using your example expression to make a different point.   To use your table in its current form, you would need to use an expression like that @AleksiAlkio has shown

To use an expression so that you do not need to hard code values, you would need to change the data structure of your "30 Days History" table.  It currently is in a non-normal form - which is shortened way to say that each time you need to add a NEW currency to the table, the table structure itself needs to be changed by adding a NEW column(s) for the currency.

Instead, the table should be normalized and in this form (note that a single row represents a single exchange rate):

ID	Clean Date	Currency Code	Exchange Rate to GBP
aaa	02/07/2024	EUR	0.84708
bbb	02/07/2024	USD	0.78900
ccc	01/07/2024	EUR	.84910
...	...	...	...

The using the table above the expression would be:

ANY(select(30 days history[Exchange Rate to GBP],
           AND([_thisrow].[Date]=[Clean Date], 
               [_THISROW].[Code]=[Currency Code])
    )
) 

I hope this helps!!