Hello my calculated column looks through a customer query for specific key words and returns a value and adds them all up. The higher the number, the more likely it will have the type of data we are looking for. It works fine BUT I want to streamline this, as will need to make similar calculated columns.
var _data=IF ( ISBLANK ( SEARCH ( "add", 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),
var _find=IF (ISBLANK ( SEARCH ( "layer", 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),
var _globe=IF (ISBLANK ( SEARCH ( "globe", 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),
var _request_to_add=IF (ISBLANK ( SEARCH ( "request to add", 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),
I will have multiple calculated columns, looking for these key words. As I add more words, I don’t want to have to update all of the calculated column invidually. I want to update one master list of keywords. This code doesn’t work obviously, but you will get what I am going for:
Var cust_query = IF ( ISBLANK ( SEARCH ( LIST OF KEY WORDS, 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),
This would be a similar calculated columns but it searches for the same words in different columns:
Var cust_response = IF ( ISBLANK ( SEARCH ( LIST OF KEY WORDS, 'report'[CUSTOMER_RESPONSE], 1, BLANK () ) ),
These there needs to be this list of words - its not a variable…what would it be?
"request to add"
Can anyone give me some advice on some scripting or functions to look into? Thanks in advance for any assistance you can offer
After my research, you could try this way:
Use LIST OF KEY WORDS to add a fact table
Use this formula to create a calculate table
Table = var _table=GENERATE(report,'Fact') return var _table2=ADDCOLUMNS(_table,"result",IF ( ISBLANK ( SEARCH ( 'Fact'[Search], report[CUSTOMER_QUERY], 1, BLANK () ) ), 0, 1 ) ) return _table2
Apologies for the lay reply, I have been on extended sick leave.
Your solution looks like it would work, but how do a i creat "fact table"? I am having problems finding it in the menus/functions of powerbi. Or is it just a spreadsheet that I import? A column of the key search words I want to look for in the the data?