topic Re: Calculated Column to Check if Field CONTAINS Value From Another Table Column in DAX Commands and Tips

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

jereaallikko — Thu, 14 Jul 2022 06:32:31 GMT

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.

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

Jihwan_Kim — Thu, 14 Jul 2022 07:31:36 GMT

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

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

Anonymous — Tue, 19 Jul 2022 05:59:29 GMT

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