Table 2 Contains string of table 1

[awitt]

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]

@awitt also can you try to add this as a measure to see if that changes the performance:

Contains Measure = 
VAR __tracking = 
SUMX ( TrackingDataDetail, CONTAINSSTRING (  MAX ( 'Order Table'[Tracking Number] ), TrackingDataDetail[Tracking Number] ) + 0 )
RETURN
IF ( __tracking > 0, 1, 0 )

✨ Follow us on LinkedIn and  to our YouTube channel

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 ❤ 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!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@awitt how big is your tracking number table? That's what is going to drive the performance.

[parry2k]

@awitt sure, I'm not surprised, because it is trying to match each record in tracking detail.

Maybe PQ is the best solution here and will be faster. Let me take a look at that.

[awitt]

I think really I need to do some work on the source data. I'd love a simple fix within Dax but I'm not sure it'll happen. 

[smpa01]

@awitt  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 <> BLANK (), 1, 0 )

[parry2k]

@awitt 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 > 0, 1, 0 )

✨ Follow us on LinkedIn and  to our YouTube channel

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 ❤ 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!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[awitt]

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. 

[awitt]

Sure.

Sample pbix file.  

[parry2k]

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