Forum Discussion

steambucky's avatar
steambucky
Icon for Helper III rankHelper III
7 years ago

How do I streamline my calculated columns

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

Need Help

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      steambucky
      Icon for Helper III rankHelper III
      7 years ago

       

      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