Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Contains/search

Hi there,

I am struggeling with a Contains formula. Can you please help me?

I want to create a columns with DAX formula that is :

If rows in Column A contains the value from Column B then row value of Column C.

Column B and C are from a same table and A is from another table.

I don't want to use lookup value, because the Column B is part of the text in Column A, and it is not fully matching.

Should I use Contains or Search? or anything else? and how would it be?

Hope that makes sense.

Thank you so much!

Bijan

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Assume that you have the below 2 tables data

    Table1:

    Table2:

    2. Create a calculated column as below in Table1 which has Column A

    Column = 
CALCULATE (
    MAX ( 'Table2'[C] ),
    FILTER ( 'Table2', SEARCH ( 'Table2'[B], 'Table1'[A], 1, 0 ) > 0 )
)

    If the above one can't help you get the desired result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

6 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    Try something like this:

    VAR B_in_A =
    FILTER (
        VALUES ( Table2[Column A] ),
        CONTAINSSTRING ( Table2[Column A], Table1[Column B] )
    )
VAR Result =
    IF ( ISEMPTY ( B_in_A ), BLANK(), Table1[Column C] )
RETURN
    Result

    Replace BLANK() with whatever result you want if A doesn't contain B.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks a lot for your response!
      I am creating the new column in the table A. and it doesn't let me to add a column from Table B, as your formula order. It lets me to add table B column in the place of A in your formula.

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        3 years ago

        Anonymous My DAX was assuming you were defining a new column in Table1 (the one with Column B and Column C).

         

        If the tables are set up as Anonymous has them, you add the column to Table2 like this:

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Assume that you have the below 2 tables data

    Table1:

    Table2:

    2. Create a calculated column as below in Table1 which has Column A

    Column = 
CALCULATE (
    MAX ( 'Table2'[C] ),
    FILTER ( 'Table2', SEARCH ( 'Table2'[B], 'Table1'[A], 1, 0 ) > 0 )
)

    If the above one can't help you get the desired result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you so much! you saved my life!