Contains/search

[Bijan]

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

[v-yiruan-msft · Accepted solution]

Hi @Bijan ,

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

[Bijan]

Thank you so much! you saved my life!

[AlexisOlson]

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.

[Bijan]

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]

@Bijan 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 @v-yiruan-msft has them, you add the column to Table2 like this:

[Bijan]

Thank you so much!