Search an Enum Help?

[David_Taylor]

Hi all,
I’ve come slightly stuck on an expression for an app i’m building and can’t get my head around it.

I have 2 tables “In Stock” and “Expenses”
“In Stock” table has a VC that sums the expenses related to each individual stock item.

In the “Expenses” table I have a column called “VAT Applicable” the data type is Enum with options of “Yes” and “No”

Essentially what i’m trying to achieve is:
SELECT Expenses[Price] WHERE [Car Id] = [_THISROW].[ID] AND Expenses[VAT Applicable] = Yes

The expression formula i’ve currently got is:

SUM(
  SELECT
(
Expenses[Price],
AND(
    ([Car Id] = [_THISROW].[ID]), 
    (IN( Yes , Expenses[VAT Applicable] ) = Yes)
     )
)
  )

This expression just returns all the expenses related to that ID regardless of [VAT Applicable] being “Yes” or “No”

Thanks in advance,
Look forward to your responses,

Dave.

[Aurelien]

Hi @David_Taylor,

Can you try:

SUM(
  SELECT(Expenses[Price],
     AND(
       [Car Id] = [_THISROW].[ID], 
       [VAT Applicable]
    )
  )
)

I assume here that [VAT Applicable] is of type Yes/No.
If not, please try:

SUM(
  SELECT(Expenses[Price],
     AND(
       [Car Id] = [_THISROW].[ID], 
       [VAT Applicable]="Yes"
    )
  )
)

Let us know if that works

Cheers

[David_Taylor]

Tried the second option:

SUM(
  SELECT(Expenses[Price],
     AND(
       [Car Id] = [_THISROW].[ID], 
       [VAT Applicable]="Yes"
    )
  )
)

All working thank you very much for your help!

Think half my issue in my experimenting was having the table name in front of the column when I didn’t need it. “Expenses[VAT Applicable]”

Thanks this helped alot!

[Aurelien]

Probably, SELECT expressions may be tricky sometimes.

You are welcome !