Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |