Table 2 Contains string of table 1

Question

I'm trying to return a result (red column) based on if a value in table 2 appears in part of a value in table 1. Table 1 (left) contains orders data with a tracking number which can include additional characters added on. Table 2 (right) contains the tracking number detail and the tracking number itself does not contain those additional characters which prevents me from having an easy 1-1 match. They are the same tracking number however and should be evaluated together.

I've tried different CONTAINSSTRING arguments but they do not seem to work. Many to many relationship btw the tables.

parry2k · Answer

awitt&nbsp;can you throw data in sample pbix file and share using one drive/google drive, remove any sensitive information before sharing.&nbsp;

awitt · Answer

Sure.&nbsp;Sample pbix file. &nbsp;

parry2k · Answer

awitt&nbsp;add a column in order table using following expression:
Contains = 
VAR __tracking = 
SUMX ( TrackingDataDetail, CONTAINSSTRING (  'Order Table'[Tracking Number] , TrackingDataDetail[Tracking Number] ) + 0 )
RETURN
IF ( __tracking &gt; 0, 1, 0 )
&nbsp;
✨ Follow us on LinkedIn and&nbsp;&nbsp;to our YouTube channel 
&nbsp;
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤&nbsp;Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation! 
&nbsp;
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
&nbsp;

smpa01 · Answer

awitt&nbsp; you can use a measure like this
Measure =
VAR _len1 =
    FIND ( " -", MAX ( 'Order Table'[Tracking Number] ), 1, 0 )
VAR _len2 =
    IF ( _len1 = 0, 0, _len1 - 1 )
VAR _string =
    CONVERT (
        IF (
            _len1 = 0,
            MAX ( 'Order Table'[Tracking Number] ),
            LEFT ( MAX ( 'Order Table'[Tracking Number] ), _len2 )
        ),
        INTEGER
    )
VAR _x =
    CALCULATE (
        MAX ( TrackingDataDetail[Charge Desc] ),
        TREATAS ( { _string }, TrackingDataDetail[Tracking Number] ),
        REMOVEFILTERS ( 'Order Table' )
    )
RETURN
    IF ( _x &lt;&gt; BLANK (), 1, 0 )

&nbsp;

awitt · Answer

This works on the sample but my two data tables each have over 5m records, using a SUMX function won't work practically. I entered the function and it's been over an hour and still hasn't completed.&nbsp;

Forum Discussion

Table 2 Contains string of table 1

9 Replies

✨ Follow us on LinkedIn and to our YouTube channel

✨ Follow us on LinkedIn and to our YouTube channel

Related content

Counting distinct list against multiple contain strings

Exporting to PDF doesn't display the images contained in a table.

contains string in power query

Check if string contains certain characters

Table not contains