Need help on searching a table for a certain string

[Tiger1]

I have two tables.  One I need to search in to find a certain string and/or column.

I have tried using - Contains(), IN() and LOOKUP(), but have failed.  In the screen shot you can see I have a dash set up with views/tables.  Table A has column [equipment].  Table B has column [item List].  I want Table A to search table B to make sure that [equipment] is IN column [Item List].  I also want Table B to search Table A to do the same thing.  I seem to have Table A working correctly, it formats the row to "Green".  Table B, I cannot get correct.

[Steve]

---

@Tiger1 wrote:

I also want Table B to search Table A to do the same thing.

---

I don't understand.

[Tiger1]

Well I want/need Table A to search as well as Table B.  Why?  So when I use formats (green for match and red for no match) I can color EACH view (Like the green row above).  Table B - has nothing in green - yet that first row SHOULD be green.  Does that make sense?

[Steve]

So...

A row in Table A should be green if its [equipment] is CONTAIN()ed in [item list] of any row of Table B.

And...

A row in Table B should be green if its [item list] CONTAIN()s an [equipment] from any row of Table A.

Correct?

[Tiger1]

YES, EXACTLY!

[Steve]

For Table A:

ISNOTBLANK(
  FILTER(
    "Table B",
    CONTAINS([item list], [_THISROW].[equipment])
  )
)

For Table B:

ISNOTBLANK(
  FILTER(
    "Table A",
    CONTAINS([_THISROW].[item list], [equipment])
  )
)

[Tiger1]

It seems to work, however every row in Table B is green, which I OLNY want the row that contains it to be green:

[Steve]

Please confirm the expression you're using. I had to correct what I posted first. What's there now is correct.

[Tiger1]

Table A:

Table B:

Is it right?

It looks right to me

[Steve]

Are you sure the literal text 4751 doesn't occur anywhere in [item list] of the second row of Table B?

[Tiger1]

I checked it, but there are like 7 rows and ALL are green.  I am pretty sure NONE conatin that.  It has to be exactly "4751" in that order, right?  

If I expand that Table/view and search for "4751" - only ONE row shows:

Now when searching:

[Steve]

Please post screenshots configurations of the equipment column of Table A and the item list column of Table B.

[Tiger1]

Table A, showing [equipment]:

Table B:

[Steve]

Is the dashboard an interactive dashboard? If so, this isn't going to work.

[Tiger1]

Yes it is interactive.   I can change that.

[Steve]

Yeah, if both tables A and B are filtered slices, this'll work.

[Tiger1]

Table A - (Which seems to be working) - Does not have a filtered slice.  Table B - I have a filtered slice on it because i ONLY wanted to see the rows that were of the same: [branch] and [customer] as the one selected in Table A.  But this is a interactice Dashboard.

[Steve]

As long as it's an interactive dashboard, it won't work.

[Tiger1]

So I just put the expression into my slice for that view AND IT NOW WORKS!  I also had it in the Format rules, but also put it in the slice.  I am going to test it some more, but it seems to be working....  Thanks STEVE!

[Tiger1]

Steve, 

   So I added a column to show the matching ticket from Table B (if there is one).  It works BUT only works if you have EXATLY the data string.

For example,

 If [Equipment] contains the data - "193865" - IT WORKS:

However, you you have anything more added in [Equipment] - It now doesn't work:

How can I fix this?

[Steve]

How would you instruct a human to do it? Knowing that will guide my answer.

[Tiger1]

I want to be able to find the correct Dispatch ticket that has ANY matching string in the [equipment] column in the [note] column of THAT dispatch ticket.  I want to display that in my Virtual Column [Matching Dispatch Ticket].  Does that make sense?

[Tiger1]

I want to add,

  This is what I have and it works, but just doesn't work when something else is added (Mentioned above)

SELECT(Backup Dispatch Query[Dispatch_Ticket],
AND(
[Closed_Status] = "open",
CONTAINS([Notes], [_THISROW].[equipment]),
[_THISROW].[Company Name] = [Customer_Name]
)
)

[Tiger1]

Steve,

  Could it be my slice on that view?

[Steve]

---

@Tiger1 wrote:

Does that make sense?

---

Nope. This is the first I've heard of a Note column.

[Tiger1]

Sorry - I changed [Item_list] to [notes] column.   

[Steve]

How would a human match that equipment value, 193865 pump, to values in the Notes columns of rows of the Backup Dispatch Query table? Do I have to know that only 193865 is what I should be matching? How would I know that? Why would I not also try to match pump?

[Tiger1]

Very good questions.  I need to think about this.  I am really not sure.  But you are right.  How would I know which to match.