Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello BI Community !
I have a column that contains a title.
This title consists of "keywords" between brackets.
These first work between brackets refers to a "Category".
How can I have a column named "Category" that extract the keyword between bracket in my title ?
Example :
What I want, is to put a condition that looks like :
If I have a [ALL] in my title, I want to put in my category to have [All]
If I have a [Stocks] in my title, I want to put in my category to have [Stocks].
Can anyone help me please ?
Many thanks !
Zakaria
Solved! Go to Solution.
Found the solution here : http://stackoverflow.com/questions/18059607/search-for-distinct-word-dax-formula
Here's the DAX code :
Column = IF(IFERROR(SEARCH("[ALL]";'Backlog Live SM9'[Title]);-1)<> -1;"ALL"; IF(IFERROR(SEARCH("[Stocks]";'Backlog Live SM9'[Title]);-1)<> -1;"Stocks"; IF(IFERROR(SEARCH("[Opti]";'Backlog Live SM9'[Title]);-1)<> -1;"Opti" )))
Anyone ? Please ?
Found the solution here : http://stackoverflow.com/questions/18059607/search-for-distinct-word-dax-formula
Here's the DAX code :
Column = IF(IFERROR(SEARCH("[ALL]";'Backlog Live SM9'[Title]);-1)<> -1;"ALL"; IF(IFERROR(SEARCH("[Stocks]";'Backlog Live SM9'[Title]);-1)<> -1;"Stocks"; IF(IFERROR(SEARCH("[Opti]";'Backlog Live SM9'[Title]);-1)<> -1;"Opti" )))
If you want to solve this in the Query Editor using M instead of using DAX (personally I like to keep all transformation in Query Editor) it can be done by adding this step as the last step in the advanced editor.
#"Add Category" = Table.AddColumn(#"Name of previous step", "Category", each if Text.PositionOf([Title], "]", Occurrence.First) = -1 then "" else Text.Start( [Title], Text.PositionOf([Title], "]", Occurrence.First) +1 ) ) in #"Add Category"
I created a small example:
Hi All,
My question similar like this, but i involving around 30 of keywords.My keywords is factory.
If F01, column category equals to F01,
IF F02, column category equals to F02,
.
.
.
IF F30,column cateogry equals to F30.
The problem is i have to write so many condition, It is there any simplest way?
Thanks!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |