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
    5 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!

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous ,

Try a new column like this in table 1

 

new column =
var _vh = sumx(filter(Table2,SEARCH(Table[Vehicle],Table[Comment],,0)>0,1,0)+0
var _food = sumx(filter(Table2,SEARCH(Table[Food],Table[Comment],,0)>0,1,0) +0
return
if(_food >0, "Food","Vehicle")

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak thank you for the possible solution. However, what if I have many more categories, and these categories could change as it is based on a user input excel file? The format of table 2 can change. It is just an example structure that i put in. Maybe its better to have it pivoted.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        amitchandak even if I pivot, I am unsure what the solution would be. I am looking to do this in PowerQuery not in DAX. Is this possible? Thank you.

  • 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
      5 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!