Forum Discussion

markhay's avatar
markhay
Frequent Visitor
8 years ago
Solved

Sentiment Analysis Measure

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

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

5 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      markhay
      Frequent Visitor
      8 years ago

      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

      namereview
      ABrilliant Service throughout
      BCaring and competent
      CMade us both feel at ease
      DThey gave us several options on next steps and costs, with no expectation either way.
      EScott was very helpfull and has gone above & beyond yo support us. Fantastic service!!
      FExcellent service

       

      PositiveSentiment Table

      Language
      compassion
      caring
      care
      communication
      kind
      helpful
      staff
      great
      grateful
      happy
      save
      saved
      friendly
      excellent

       

       

      Intended Outcome

      CategoryJanFebMar
      Positive Sentiment82356
          

       

      Appreciate your help

      • markhay's avatar
        markhay
        Frequent Visitor
        8 years ago
        Hi all

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

        Thanks
        Mark