How to use contains when searching two tables

Tiger1
Participant V

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]))))
)
)

0 22 179
22 REPLIES 22

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.

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?

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?

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] )

I meant within the entire rest of your expression…

oh, i gotcha…

3X_1_f_1fc0501a223dc057107f936daab8d634188fd9c5.png

Ok. I think you did it!

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

Thanks again!

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] )
  )
)

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?

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

It is now showing one date value. However, because it is a LIST Type - I cannot count the days from TODAY(). I need to the app to count back from TODAY() to that date given and show the number of days.

Using ANY - worked great. Thanks again. I owe you.

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] )
  )
)
Top Labels in this Space