Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |