Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jereaallikko
Helper III
Helper III

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

NEWB.PNG

 

 

 

 

 

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

NEWB2.PNG

 

 

 

 

 

 

 

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:
NEWB4.PNG

 

 

 

 

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.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors