Get a value based on contain values

[Tiger1]

I have a table.  In this table I have columns:

[customer number]

[document type]

[document number]

[original_trx_amt]

I need the value of [original_trx_amt] if:

[document type] = "Credit memo"

if this row [document number] contains the same text as another row where [document type] = "sales / Invoices" and both rows have the same [customer number].

[lynchk21 · Accepted solution]

If I understand you correctly, I would:

1. make a slice on your table called "sales" with the expression [document type] = "sales / Invoices"
2. Make a virtual columns with an expression like:
   • ANY(SELECT(table[original_trx_amt],AND([customer number]=[_thisRow].[customer number],CONTAINS(sales[document number],[_thisRow].[document number]))))