Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I 've got two tables (not related to each other). Table1 has survey comments in text format and table2 has hot-words to categorize a survey into three main categories namely POSITIVE, NEGATIVE & GENERAL. I have written the code below but unfortunately the output is pretty weird as the output insert multiple results against every comment..probably it keeps looking into the comments and whenever it finds a match it insert the result in short ..it does the match but returns multiple values against a single record. Any help & correction will be highly appreciated Here is the code
CommentGrade = VAR Matches = CALCULATETABLE( GENERATE( Sheet1,FILTER( CSATAllData, SEARCH( [Perspective Name],[Comments], 1, 0 )>0) ) ) RETURN CONCATENATEX( Matches, [PerspectiveGrade],",")
Solved! Go to Solution.
Hi @bukhari1979,
You can try to use below measures to get match key from other table.
Sample:
Matched = VAR _text = SELECTEDVALUE ( Table2[Text] ) RETURN CONCATENATEX ( FILTER ( ALL ( KeyTable ), SEARCH ( [Key], _text, 1, -1 ) > 0 ), [Key], "," ) Matched Category = VAR _text = SELECTEDVALUE ( Table2[Text] ) RETURN CONCATENATEX ( CALCULATETABLE ( VALUES ( KeyTable[Category] ), FILTER ( ALL ( KeyTable ), SEARCH ( [Key], _text, 1, -1 ) > 0 ) ), [Category], "," )
Regards,
Xiaoxin Sheng
HI @bukhari1979,
Please provide some sample data for test, I think it will be help for coding formula.
Regards,
Xiaoxin Sheng
Hi @bukhari1979,
You can try to use below measures to get match key from other table.
Sample:
Matched = VAR _text = SELECTEDVALUE ( Table2[Text] ) RETURN CONCATENATEX ( FILTER ( ALL ( KeyTable ), SEARCH ( [Key], _text, 1, -1 ) > 0 ), [Key], "," ) Matched Category = VAR _text = SELECTEDVALUE ( Table2[Text] ) RETURN CONCATENATEX ( CALCULATETABLE ( VALUES ( KeyTable[Category] ), FILTER ( ALL ( KeyTable ), SEARCH ( [Key], _text, 1, -1 ) > 0 ) ), [Category], "," )
Regards,
Xiaoxin Sheng
Hi, I am relatively new to Power BI and this is my first time posting.
I tried to adapt the above solution to simply produce a table showing the number of times a specific term from my mapping table appeared in my base data table. Unfortunately it didn't perform a wildcard match, just an exact match. How do I get it to do a wildcard match?
Here's the code that I used (not sure if I'm posting correctly here):
Map | |
Code | Job Function 2019 |
RESPNBLT1 | Corporate / General Management |
RESPNBLT2 | Technical / Engineering |
RESPNBLT3 | Administration |
RESPNBLT4 | Business Development / Project Management |
RESPNBLT5 | Consulting |
RESPNBLT7 | Other (please specify) |
etc. |
Data | |
Row | Job Function Codes |
1 | RESPNBLT4, RESPNBLT8, RESPNBLT19, RESPNBLT16, RESPNBLT32 |
2 | RESPNBLT14 |
3 | RESPNBLT17 |
4 | RESPNBLT30, RESPNBLT14, RESPNBLT12, RESPNBLT28 |
Any tips or suggestions would be greatly appreciated.
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |