Multiple word search and return - basic NLP (Sentiment)

Anything related to PowerPivot and DAX Formuale
sayth
Posts: 17
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: 929
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 Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

sayth
Posts: 17
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: 929
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 Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

sayth
Posts: 17
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 1 guest