Solved: Problem with Select and Lists

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").

See also:

View solution in original post

Steve

In ([Sale Currency] = PRICING[Currency]), PRICING[Currency] is a table-column reference, which produces a list containing all of that column’s values, so you’re comparing a singular value ([Sale Currency]) to that list of values. It’s not at all clear how this expression is related to the SELECT() expression, so I don’t know what else I can offer.

baggster55

Perhaps I should ask more generically. I have one table with the following columns and example data:

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

I am trying to pull from this table a Price based on a SKU and CCY combination, i.e. "get me the Price for something that has SKU=SKU1 and CCY=USD.

Thanks

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").