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!

     

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.