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 |
---|---|
42 | |
30 | |
25 | |
23 | |
13 |