Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Does a column contain text from another column?

Hello! If I have two tables: Table 1: Comment I like to eat hot dogs I don't like pineapple A red car Table 2: Vehicle Food car dog   apple   How could I use the c...
Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  Anonymous ,

     

    First unpivot table2,and you will see:

    Then create a measure as below:

    Category = 
var _comment=SELECTEDVALUE(Table1[Comment])
var _value=CONCATENATEX(FILTER(DISTINCT('unpivot-Table2 (2)'[Value]),CONTAINSSTRING(_comment,[Value])),[Value],",")
Return
CALCULATE(MAX('unpivot-Table2 (2)'[Attribute]),FILTER('unpivot-Table2 (2)','unpivot-Table2 (2)'[Value]=_value))

    And you will see:

    For the related .pbix file,pls see attached.

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak even if I try to use DAX, it is unclear to me how I could use the columns from two seperate tables in this way as they are unrelated. So I couldn't mix and match the columns like this. If DAX is the easier way to solve this problem I would be happy to go with it.

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi  Anonymous ,

 

First unpivot table2,and you will see:

Then create a measure as below:

Category = 
var _comment=SELECTEDVALUE(Table1[Comment])
var _value=CONCATENATEX(FILTER(DISTINCT('unpivot-Table2 (2)'[Value]),CONTAINSSTRING(_comment,[Value])),[Value],",")
Return
CALCULATE(MAX('unpivot-Table2 (2)'[Attribute]),FILTER('unpivot-Table2 (2)','unpivot-Table2 (2)'[Value]=_value))

And you will see:

For the related .pbix file,pls see attached.

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!