Formula for dashboard select one or both values from a cell

[gekka]

I have conditions that basically i have Report Table that have colomn Date, Job, Worker, etc.

In My App, one Job can have multiple Worker. So the cell from Worker collomn sometimes hold two names (e.g John, Smith).

I have dashboard to filter that data when i need it. The problem is my formula only pick the cell that only hold one value (e.g John - one job one worker). If the cell have two name (John, Smith) the formula not pick any name from the cell so overall result filter not accurate because the cell that hold two names not recorded.

What formula to write so i can get data from the cell that hold two names. So i can pick either one of them or both.

My current formula is :
AND(IF(ISNOTBLANK(INDEX(FilteredJobSlice[Worker_ID],1)),[Worker_ID]=(FilteredJobSlice[Worker_ID],1),true),
...............

[TeeSee1 · Accepted solution]

You can use IN() or CONTAINS() depending on the column type of [Worker_ID], ENUMLIST or TEXT. 

[Steve · Accepted solution]

ISNOTBLAN(
  INTERSECT(
    [Worker],
    SPLIT(("" & FilteredJobSlice[Worker_ID]), " , ")
  )
)

[TeeSee1 · Accepted solution]

You can use IN() or CONTAINS() depending on the column type of [Worker_ID], ENUMLIST or TEXT. 

[gekka]

Also Works

CONTAINS([Worker_ID],SPLIT(FilteredJobSlice[Worker_ID],","))

[gekka]

 Still don't get it.

On the picture above the result will be cell 2 and 3 showing. If i search "Worker Two" then cell 1 will show. Notice that even cell 4 have "worker One" or "Worker Two" they will not show in search result. The only cell number 4 will show if only i search for "Worker One and Worker Two at the same time.

Here is my modified in my Row Filter Contition now:

AND(
IF(ISNOTBLANK(INDEX(FilteredJobSlice[Worker_ID],1)),in([Worker_ID], SPLIT(CONCATENATE(FilteredJobSlice[Worker_ID]),",")),true),.........

[Steve · Accepted solution]

ISNOTBLAN(
  INTERSECT(
    [Worker],
    SPLIT(("" & FilteredJobSlice[Worker_ID]), " , ")
  )
)

[gekka]

It works.Thx

[gekka]

Hi, Steve the script is work with draw back for my app. When i create chart with Worker as collumn it show all the worker from the cell. How to make only single worker name that only appears?

[gekka]

Now iam trying different approach. I Split column worker into two column so the worker one and worker two in different Column.

Right now i am working on your answer in 

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Obtain-value-based-on-two-other-columns/m-p/259...

can you help me put it together?

[gekka]

IN(WorkerJobSlice[Worker1], ([Worker1], [Worker2]))

This formula only take value from [Worker1].

How to take value from {worker2] Ashwell?

[gekka · Accepted solution]

Found it.

IN(CONCATENATE(FilteredJobSlice[Worker1]), ([Worker1]+[Worker2]))

[TeeSee1]

In case you need more that two workers assigned to a work, you can retain the worker column as an ENUMLIST and create a virtual column to display in your graph.

The expression can be something like

INTERSECT(
 FilteredJobSlice[Worker],
 **your filter worker enumlist here**
)

[gekka]

It getting there! Little bit more.

It work when compared two list. The worker bar showing for each other. But when compared to two or more list it struggling to show and for a second it seem the bar will show separately for each Worker but suddenly the bar break apart showing Worker1, worker2 too.