Forum Discussion

Dane's avatar
Dane
Helper I
4 years ago
Solved

If Text Contains Statement

I have 2 related tables (one showing tracking history at different work cells and the other showing order details). I am trying to create a column in my Tracking History table that will calculate Linear Inches using the following logic:

 

If OrderDetails[PartNumber] contains "2L" then TrackingHistory[Width]*2 else (TrackingHistory[Width]*2) + (TrackingHistory[Height]*2)

 

Any recommendations?

Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi Dane ,

     

    Does this one here work for you?

    IF (
    SEARCH ( 
        "2L",
        OrderDetails[PartNumber],
        1,
        BLANK()
    ),
    TrackingHistory[Width] *2,
    ( TrackingHistory[Width] *2 ) + ( TrackingHistory[Height] *2 )
)


    I pressumed that your logical test shall search for "2L" in PartNumber, meaning "2L" could come up at different positions in a string (i.e. "ABC 2L", "2L DEF", "GHI 2L JKL" etc.). 

     

    Let me know if this helps and if not, feel free to share some sample data and I can take a deeper look into it 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

10 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    Hi Dane ,

     

    Does this one here work for you?

    IF (
    SEARCH ( 
        "2L",
        OrderDetails[PartNumber],
        1,
        BLANK()
    ),
    TrackingHistory[Width] *2,
    ( TrackingHistory[Width] *2 ) + ( TrackingHistory[Height] *2 )
)


    I pressumed that your logical test shall search for "2L" in PartNumber, meaning "2L" could come up at different positions in a string (i.e. "ABC 2L", "2L DEF", "GHI 2L JKL" etc.). 

     

    Let me know if this helps and if not, feel free to share some sample data and I can take a deeper look into it 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

    • Dane's avatar
      Dane
      Helper I
      4 years ago

      I tried your solution but could not bring in OrderDetails[PartNo] into the formula for some inexplicable reason. Can I email you the PBIX I have?

      • tackytechtom's avatar
        tackytechtom
        Most Valuable Professional
        4 years ago
        Hi Dane,

         

        I'd suggest that you upload your pbix file to a sharepoint or public dropbox folder and paste the link into the forum. With that others will be able to help you, too!

         

        Alternatively, you could just paste a few rows of your two tables OrderDetails and TrackingHistory. Make sure to explain their connection as well 

         

        /Tom
        https://www.tackytech.blog/
        https://www.instagram.com/tackytechtom/

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Try this formula in the Query Editor

    =if Text.Contains(OrderDetails[PartNumber], "2L", Comparer.OrdinalIgnoreCase) then TrackingHistory[Width]*2 else (TrackingHistory[Width]*2) + (TrackingHistory[Height]*2)

    Hope this helps.

    • Dane's avatar
      Dane
      Helper I
      4 years ago

      I tried the formula you suggested and am getting an error in the column, the bottom of the page says "Expression.Error: We cannot convert a value of type list to type text.

      Details:

      Value=[List]

      Type=[Type]