cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steambucky Member
Member

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: How do I streamline my calculated columns

hi, @steambucky

After my research, you could try this way:

Step1:

Use  LIST OF KEY WORDS to add a fact table

1.JPG

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

2.JPG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: How do I streamline my calculated columns

Hi,

 

Share a dataset and show the expected result.

Highlighted
steambucky Member
Member

Re: How do I streamline my calculated columns

 

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