Multiple word search and return - basic NLP (Sentiment)

Anything related to PowerPivot and DAX Formuale
sayth
Posts: 15
Joined: Mon Nov 02, 2015 10:57 am

Multiple word search and return - basic NLP (Sentiment)

Postby sayth » Fri Apr 21, 2017 2:06 pm

I want to be able to search text in a column in my workbook full of questions, if a word occurs in the text i want a resulting phrase to return in my Type of Question column.

As I can't include work information in a question I am creating a basic example to demonstrate.
So in the first column we have a basic statement and in the second the selected word to return.

Code: Select all

Statement                 Value
this is blue               Blue
maybe its green            Green
looks like yellow          Yellow
could be blue or green     Multiple


Now in Dax returning one is easy

Code: Select all

=IF(SEARCH("Blue",[Statement],,BLANK())>0,"Blue","Other"))


How though can I do this for multuple search terms returning different results on the terms they find so that ultimately I can present those values and count out and provide some insight. Of all responses given 22 featured "BLUE", and 12 "GREEN", and only 2 "YELLOW", therefore our respondents have a 61% preference to BLUE.

I tried doing this with a switch statement however could not get more than an error.

Code: Select all

=SWITCH([Statement],IF(SEARCH("Blue",[Statement],,BLANK())>0,1),"BLUE",IF(SEARCH("Green",[Statement],,BLANK())>0,2),"GREEN")

MattAllington
Posts: 816
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Multiple word search and return - basic NLP (Sentiment)

Postby MattAllington » Sun Apr 23, 2017 7:39 am

Have you considered doing this in power query instead? I think that is a better place for this, and could be done easily in the UI.

Or you could just do a nested if. Write a second if statement that works. Then copy the entire second statement ex the = and paste it where the "other" is
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

sayth
Posts: 15
Joined: Mon Nov 02, 2015 10:57 am

Re: Multiple word search and return - basic NLP (Sentiment)

Postby sayth » Wed Apr 26, 2017 8:22 pm

Totally have not thought about using power query. Can't imagine how i would do it there.

Sadly power query let's me down usually and i just end up using python. I keep thinking it should be good but keeps failing will have to dust it off zgain.

MattAllington
Posts: 816
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Multiple word search and return - basic NLP (Sentiment)

Postby MattAllington » Wed Apr 26, 2017 8:26 pm

If you post some sample data I can take a look for you. Or you may be interested in my Power Query Training. Http://xbi.com.au/pqt
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

sayth
Posts: 15
Joined: Mon Nov 02, 2015 10:57 am

Re: Multiple word search and return - basic NLP (Sentiment)

Postby sayth » Mon May 01, 2017 9:35 am

Thanks. I will need to follow up can't see in the course directory where NLP is covered. I have another unrelated sheet where now I need to do a similar word search algorithm on columns to determine sentiment and trends.

Can't actually post the data I am working with due to IT guidelines in my company.

EDIT possibly the https://powerbi.microsoft.com/en-us/blog/power-bi-q-and-a-natural-language-search-over-data/ feature is what I am looking for ?


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests