I am trying to get a comma-delimited list of cost centers from my Cost Source Actuals table. I would like to exclude a series of values from this list. For now, I have a column listing those exclusions (see below)

For my example, let's say my cost centers are 1,2,3,4,5,6,7. I would like to get a list like:

The formula I thought would work is:

=substitute(UniqueValues(Cost Source Actuals Unfiltered Transform:DeptID[DeptID NOT IN ({List Exclusions})]),"""","")

When I enter that, I get no results. Strangely, if I change "NOT IN" to "IN," I get everything with no exclusions. How do I do what I'm trying to do?

List Exclusions column needs to reside in the Cost Source Actuals Unfiltered Transform table for your formula to work, because the formula argument following NOT IN refers to the same destination table as the UniqueValues() itself.

But, two issues:

1. I don't think we can embed a comma-delimited list of values (label- or numeric-type) into a cell and then have that cell act as the NOT IN filter. We can only have one value per cell (although we can have more than one unique cell value per column) for purposes of the NOT IN filter.

2. Table row order is not guaranteed in Apptio, so if the two columns shown in your post reside in different tables, we have no way of knowing whether the rows will match up between the two tables.

It was a longshot. Thanks.

Is there a way to have a dynamic search the way I'm thinking? I was able to work around it by adding a flag in the CSAU Transform, but it seems like adding an extra hop to a calculation.

The only other potential option I know of is to modify the table such that we can make use of the Duplicate Rows Using Split feature, seen here with a comma in the field as the string delimiter:

This may help with splitting up one table row into multiple rows (for example, one row per list exclusion) which could then be a step toward a more dynamic lookup setup.