Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
awitt
Helper III
Helper III

Table 2 Contains string of table 1

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. 

 

awitt_0-1641235622536.png

 

9 REPLIES 9
parry2k
Super User
Super User

@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 YouTube.gif 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Super User
Super User

@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 )

 

smpa01_0-1641243693403.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
parry2k
Super User
Super User

@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 YouTube.gif 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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Helper III
Helper III

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.