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 Lin...
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/

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]

 

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Share the download link of your PBI file.