Sentiment Analysis Measure

[markhay]

Hi

Looking for assistance with a measure. I have a a number of tables, 1 with customer review data and others with keywords that could indicate a particular sentiment. I would like to run a count on the review column (Merchant[review] to give me a hit count for the words in the reference tablesClinicalSentiment[Language]. 

Advice appreciated!

Mark

[v-yulgu-msft]

Hi @markhay,

Please share sample data, including detailed records in Merchant and ClinicalSentiment and table relationship. Also show us your desired output so that I can test for you.

Regards,

Yuliana Gu

[markhay]

Hi @v-yulgu-msft

Thanks for coming back to me There are no existing relationships between the Merchant and Sentiment Tables. Samples below of the main table and one of the tables containing the sentiment words. From the example I would expect rows B, E & F to trigger the count:

Merchant Table

name	review
A	Brilliant Service throughout
B	Caring and competent
C	Made us both feel at ease
D	They gave us several options on next steps and costs, with no expectation either way.
E	Scott was very helpfull and has gone above & beyond yo support us. Fantastic service!!
F	Excellent service

PositiveSentiment Table

Language

compassion

caring

care

communication

kind

helpful

staff

great

grateful

happy

save

saved

friendly

excellent

Intended Outcome

Category	Jan	Feb	Mar
Positive Sentiment	8	23	56

Appreciate your help

[markhay]

Hi all

Anyone have any further thoughts on how this could best be done?

Thanks
Mark

[v-yulgu-msft · Accepted solution]

Hi @markhay,

You could new a calculated table via this formula:

CrossJoin Table =
FILTER (
    ADDCOLUMNS (
        CROSSJOIN ( Merchant, PositiveSentiment ),
        "Column1", IF ( ISERROR ( FIND ( [Language], [review] ) ), 0, 1 )
    ),
    [Column1] = 1
)

Please be aware that FIND function is case-sensitive.

Best regards,

Yuliana Gu

[markhay]

@v-yulgu-msftAmazing. works a treat. thank you