Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
markhay
Frequent Visitor

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

 

1 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
)

2.PNG

 

Please be aware that FIND function is case-sensitive.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi all

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

Thanks
Mark

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
)

2.PNG

 

Please be aware that FIND function is case-sensitive.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.