Solved: Problem with Select and Lists - Page 2

baggster55 · 05-13-2020 07:33 AM

Hi - having a problem with a Select statement. I keep getting a “cannot compare text with a list” errors. Here is the background:

I have a table called Pricing. It has a list of prices organized by SKY and Currency, for example;

SKU1 USD Price 1
SKU1 EUR Price 2
SKU2 USD Price 3
SKU2 EUR Price 4

I am trying to pull the correct Price, based on SKU and Currency, from the Pricing Table into my Sales table/form. I have put together the below Select expressions:

ANY(SELECT(Pricing[Price (with VAT)],AND(([SKU] = [_ThisRow].[SKU]),([Currency] = [_ThisRow].[Sale Currency])),FALSE))

SKU and Currency in both tables are Text, and Prices in both tables are Decimal. I keep getting the following error: Cannot compare Text with List in ([Sale Currency] = PRICING[Currency])

Any idea? Thanks in advance.

Steve

Try:

ANY(
  SELECT(
    table[Price],
    AND(
      (wanted-sku = [SKU]),
      (wantedf-ccy = [CCY])
    )
  )
)

replacing table with the name of that table; wanted-sku with an expression that provides the SKU you want to match (e.g., [_THISROW].[SKU], or "SKU"); and wanted-ccy with an expression that provides the currency identifier you want to match (e.g., [_THISROW].[CCY], or "USD").