Calculated Column to Check if Field CONTAINS Value From Another Table Column

[jereaallikko]

Hi all,

I am trying to create a calculated column to check if a column contains value from mapping table column, if not, then to check another column whether it contains value from mapping table column. Let me explain better:

I have 2 tables:

• Table1 is the main table containing lots of records and columns about transactions

• Table2 is used for mapping, contains more detailed information about products (pieces)

So, I would like to create a calculated column in 'Table1' to check (in this order):

• IF 'Table1'[Piece] Contains any value from 'Table2'[Piece], then return the value from 'Table2'[Piece]

If this criteria is not met, then I would like to check the same rules from the Title:

• IF 'Table1'[Title] Contains any value from 'Table2'[Piece], then return the value from 'Table2'[Piece]

If neither one is met, I would like to return 'Table1'[Piece], even if its left blank.

Here is still the desired output:

Then I would like to create a relationship between the created column and 'Table2'[Piece], to get more detailed information about the products.

Any tips from anyone? If there are more information needed, please don't hesitate to ask.

[v-shex-msft]

Hi @jereaallikko,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Output CC =
VAR _firstcondition =
    LOOKUPVALUE ( Table2[Piece], Table2[Piece], Table1[Piece] )
VAR _secondcondition =
    SUMMARIZE (
        FILTER ( Table2, CONTAINSSTRING ( Table1[Title], Table2[Piece] ) ),
        Table2[Piece]
    )
RETURN
    IF (
        _firstcondition <> BLANK (),
        _firstcondition,
        IF ( _secondcondition <> BLANK (), _secondcondition, BLANK () )
    )