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.
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…
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] )
)
)
User | Count |
---|---|
59 | |
25 | |
13 | |
12 | |
6 |