Using Keywords to Create a Column

[mattprenz]

I am creating a project time tracking dashboard and my hopes are to allow for flexibility of adding new projects/keywords to track. I would like to be able to enter keywords (in a separate table possibly) and then create another column in my outlook calendar table to add a project title. I then use that project title in graphs.

Ideally it would look something like this:

And then I would use it to create a new nested IF statement by iterating through the table somehow to create a column like this:

Project = IF(SEARCH("testing", MyCalendar[Subject],,0),"Project Testing", IF(SEARCH("budget", MyCalendar[Subject],,0),"Budget",IF(SEARCH("app", MyCalendar[Subject],,0),"App Development")))

Perhaps there is an easier way to do this, I'm looking to the experts out there to help a new Power BI user. Thanks in advance for your help!

[v-fengyz-msft]

@mattprenz

So, would you like to input fields on report at run time or just refresh data then generate proper values based on the enter value?

If it’s former, as far as I know, currently it’s not supported in Power BI desktop. You may submit a request on Power BI Idea.

If it’s latter,  assume you add a new keyword e.g. test2, then you would like to check if it exists in your Subject Column of MyCalendar table, so you need to add external nested if function to check that. To tune the expression, you may try to use contains function like below:

Column = CONTAINS(Table2, Table2[Value], Table1[Value])

Please have a look at this similar discussion: 

If column contains values from column in another table...

Regards,

Pirlo Zhang

[mattprenz]

In a perfect word input fields on the report would be ideal, but I have read several articles requesting that feature. I was planning on hard coding the variables into a separate table, but I would like to dynamically search for those variables in the calendar subject column.

Your contains function idea might work. Although it wouldn't allow me to add new rows without manually changing the nested if. I was hoping to add another layer to the nested if depending on the number of rows. (no clue if it is even possible)

[v-fengyz-msft]

@mattprenz

If keywords are limited, you may consider to hard code the expression. 

To generate the nested if depends on the number of rows dynamically, as far as I know, it's not supported now.  To workaround this, just try using Contains() function, see what happens.

Regards,

Pirlo Zhang