How do I streamline my calculated columns

[steambucky]

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.  

_customer_query =

var _data=IF ( ISBLANK ( SEARCH ( "add", 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),

0,1)

var _find=IF (ISBLANK ( SEARCH ( "layer", 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),

0,1)

var _globe=IF (ISBLANK ( SEARCH ( "globe", 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),

0,1)

var _request_to_add=IF (ISBLANK ( SEARCH ( "request to add", 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),

0,1)

return _data+_request_to_add+_globe+_find

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:

_customer_query=

Var cust_query = IF ( ISBLANK ( SEARCH ( LIST OF KEY WORDS, 'report'[CUSTOMER_QUERY], 1, BLANK () ) ),

0,1)

Return cust_query

This would be a similar calculated columns but it searches for the same words in different columns:

_customer_response=

Var cust_response = IF ( ISBLANK ( SEARCH ( LIST OF KEY WORDS, 'report'[CUSTOMER_RESPONSE], 1, BLANK () ) ),

0,1)

Return cust_response

These there needs to be this list of words - its not a variable…what would it be?

"add”

"layer"

"globe"

"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

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[v-lili6-msft]

hi, @steambucky

After my research, you could try this way:

Step1:

Use  LIST OF KEY WORDS to add a fact table

Step2:

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

Best Regards,

Lin

[steambucky]

Hi v-lili6-msft

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? 

Cheers, 

steambucky