How to use contains when searching two tables

I need to use CONTAINS to search in Table A a column value and compare it to a column value in Table B.

Is this possible?

I have this and it obviously is not working:
SELECT(Rental Tickets[Min_Start_Date],
AND(
[Customer_Name]= [_THISROW].[Customer_Name],
Contains([Ticket_Action], “On Rent”,
CONTAINS(Rental Tickets([Item_List], [_ThisRow].[Inventory_Item_ID]= [Inventory_Item_ID]))))
)
)

Why do you think CONTAINS() is the appropriate expression? Do you understand the difference between that and IN()?

This portion of your expression is so wrong that I can’t even figure out what you’re trying to do. Can you explain it further? Your first argument is a List, but you’re using CONTAINS (see above), and your second argument is an equality, which will return a TRUE or FALSE value, which makes zero sense in this circumstance.

1 Like

Yeah. I have lost all concepts here. I need to look in a Table B - in a column. In that column I need to look for an [ID]. So the column will have data but will contain somewhere in it an [ID]. I need to match that [ID] to the [ID] in this table - Table A. If the {ID] matches from Table A and this column contains that same [ID] then i need that date - [Min_Start_date] from the Table A.

Writing “Table A” and “Table B” is not helpful when your posted expression has actual Table names. Which matches to which?

2 Likes

Table A = Assets
Table B = Rental Tickets

Table A - is where I am doing this expression.
Table B- is where I need to compare the [ID] (in Table A) to the values in column [Item_List]. If [Item_list] contains the [ID] that matches the [ID] in Table A - then I want to show the [Min_Start_date] from Table A.

So the [ID] from Assets just has to exist anywhere in the entire Rental Tickets Table?

1 Like

Yes - In the column [Item_List] in Table B - Rental Tickets…

OK I think I understand now. Try this:

CONTAINS( CONCATENATE( Rental Tickets[Item_List] ) , [_THISROW].[Inventory_Item_ID] )
1 Like

I meant within the entire rest of your expression…

1 Like

oh, i gotcha…

To be more clear,try this:

SELECT(
  Rental Tickets[Min_Start_Date],
  AND(
    [Customer_Name]= [_THISROW].[Customer_Name],
    Contains([Ticket_Action], “On Rent” ),
    CONTAINS( CONCATENATE( Rental Tickets[Item_List] ) , [_THISROW].[Inventory_Item_ID] )
  )
)
1 Like

image

1 Like

Ok. I think you did it!

Thanks I was completely lost. I will check the data.

Thanks again!

2 Likes

If that doesn’t work, you might be wanting this instead, your intention really isn’t very clear.

SELECT(
  Rental Tickets[Min_Start_Date],
  AND(
    [Customer_Name]= [_THISROW].[Customer_Name],
    CONTAINS( [Ticket_Action] ,  “On Rent” ),
    CONTAINS( [Item_List]     ,  [_THISROW].[Inventory_Item_ID] )
  )
)
1 Like

Again, I am sorry. My expression building is not very good. I appreciate your help.

Is it possible to only give one [Min_Start_date]? I am getting all data in a list - because I used SELECT. But I only need the one date [Min_Start_Date] that is given for that match.

Here the assets table gives me the list of assets. When I click on an asset, I see all the date values instead of just the one asset row I clicked on:

Does using the second posted expression fix that issue? Otherwise, how do you determine which is the correct date?

1 Like

Ok. You are extremely good. Your second expression fixed it. Sorry, I didn’t check it earlier. Dang. Thanks again!