Trying to get only instances where the Yes/No Condition exists

Hi there,

I am trying a new approach to an issue I had previously.

Here is the issue: I am trying to get only the instances where ERG shows up in the TestID ERG List.

Here is my expression that I am using:

Creating a Virtual Column:
Select(TESTID List[Test ID], IN([RG_NAME], (SPLIT(TESTID LIST[ERG LIST],","))))

With this I am close, but I am getting the whole list of TESTID List[Test ID], I just want the intances where RG_Name appears in the ERG List and grab the Test ID when it appears.

Example:
Table: TestID List

Table: ERG_TechRole

Using the function above brings back all TestIDโ€™s, not just TS_Security

Any help is appreciated.

Solved Solved
0 3 223
1 ACCEPTED SOLUTION

Steve
Participant V

Try:

SELECT(
  TestID List[Test ID],
  IN([_THISROW].[RG_Name], [ERG List])
)

View solution in original post

3 REPLIES 3

Steve
Participant V

Try:

SELECT(
  TestID List[Test ID],
  IN([_THISROW].[RG_Name], [ERG List])
)

Thank you so much Steve. That worked perfectly.

Sorry my wording isnโ€™t the greatest, and thank you for fixing it.

I was wondering, could you tell me the difference between my equation and yours?

Your expression:

SELECT(
  TestID List[Test ID],
  IN(
    [RG_Name],
    SPLIT(
      TestID List[ERG List],
      ","
    )
  )
)

My expression:

SELECT(
  TestID List[Test ID],
  IN(
    [_THISROW].[RG_Name],
    [ERG List]
  )
)

You were very close, actually. Mine prefixes [RG_Name] with [_THISROW]., but it isnโ€™t strictly needed in this case. The big difference is is the the list of values weโ€™re comparing to.

In my case, the expression is using [ERG List], which refers to the ERG List column value of each row in the TestID List table. In your expression, you use SPLIT(TestID List[ERG List], ","), which first uses TestID List[ERG List] to create a list of all ERG List column values in the TestID List table.

Since each ERG List column value is itself a list, and youโ€™re creating a list from those, youโ€™re creating a list of lists. SPLIT() โ€œflattensโ€ that list-of-lists into a single list of all the values in all of the component lists. Then you look to see if the RG Name value occurs in that list.

The problem here is that flattened list-of-lists includes the ERG List values from all rows in the TestID List table, which isnโ€™t really what you want to check. You only want the current rowโ€™s ERG List values.

In effect, your expression answers the question, โ€œdoes this RG Name occur in any ERG List?โ€, where mine answers the question, โ€œdoes this RG Name occur in this rowโ€™s ERG List?โ€

See also:

Top Labels in this Space