Forum Discussion

jereaallikko's avatar
jereaallikko
Icon for Helper III rankHelper III
4 years ago
Solved

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

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.

DAX
Need Help
Tip & Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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 () )
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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 () )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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