Forum Discussion
Find text string in another table
Hello all,
Our customer is creating tickets in their ticket system, which we mirror in our own ticket system.
The data of our customer looks as follows:
| SummaryCustomer | DescriptionCustomer |
| Ticket 1 | Description to Ticket 1 |
| Ticket 2 | Description to Ticket 2 |
| Ticket 3 | Description to Ticket 3 |
| Ticket 4 | Description to Ticket 4 |
| Ticket 5 | Description to Ticket 5 |
No when we mirror those tickets in our ticket system, our employees put a prefix in front of the Summary of the Customer (e.g. Customer123 Ticket 1) => see below
| Summary |
| Customer Ticket 1 |
| 12345 |
| 45678 |
| Internal OP 1 |
| 34567 |
| Cust. Ticket 2 |
| Custom. Ticket 3 |
| C. Ticket 4 |
| Customer123 Ticket 5 |
While there is no clear convention for the prefix, our summary will always contain the Summary of the Customer that comes from the first table.
In our table, I now want to find out for each item, whether it comes from the customer table, i.e. something like this:
| Summary | Source |
| Customer Ticket 1 | Customer |
| 12345 | Internal |
| 45678 | Internal |
| Internal OP 1 | Internal |
| 34567 | Internal |
| Cust. Ticket 2 | Customer |
| Custom. Ticket 3 | Customer |
| C. Ticket 4 | Customer |
| Customer123 Ticket 5 | Customer |
It has to work via comparing the text strings of the Summary fields.
Is there a way to do it? I tried using Containsstring() but failed miserably.
Thanks and kind regards
Marcel
Anonymous,
Try this calculated column in InternalTable:
Source = VAR vTable = FILTER ( CustomerTable, VAR vCustomerValue = CustomerTable[SummaryCustomer] RETURN CONTAINSSTRING ( InternalTable[Summary], vCustomerValue ) ) VAR vResult = IF ( COUNTROWS ( vTable ), "Customer", "Internal" ) RETURN vResult
1 Reply
Anonymous,
Try this calculated column in InternalTable:
Source = VAR vTable = FILTER ( CustomerTable, VAR vCustomerValue = CustomerTable[SummaryCustomer] RETURN CONTAINSSTRING ( InternalTable[Summary], vCustomerValue ) ) VAR vResult = IF ( COUNTROWS ( vTable ), "Customer", "Internal" ) RETURN vResult