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.
I have some of the ideas, but not all to create the calculated column I need.
I want to write a calculated column where the value varies depending what specific words it finds in the text.
There are the words we are looking for:
Big = 1
Black = 1
Cats = 1
Data and the value I would be expecting
I like big black cats = 3
I like big black dogs = 2
I like big ginger cats = 2
I like Black animals = 1
I like dogs = 0
I like cats = 1
I THINK that I need to write some scripting that, if written in just plain English would be like this:
Big_black_cats = IF(
(If Data contains = big then Big_black_cats = Big_black_cats +1)
(If Data contains = black then Big_black_cats = Big_black_cats +1)
(If Data contains = cat then Big_black_cats = Big_black_cats +1)
)
I know that’s NOT right, but can anyone get me started?
Solved! Go to Solution.
You can use the following to create a calculated column
CalcCats = var _black=IF ( ISBLANK ( SEARCH ( "Black", Table1[Data], 1, BLANK () ) ), 0,1) var _big=IF ( ISBLANK ( SEARCH ( "big", Table1[Data], 1, BLANK () ) ), 0,1) var _cats=IF ( ISBLANK ( SEARCH ( "cats", Table1[Data], 1, BLANK () ) ), 0,1) return _black+_big+_cats
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
You can use the following to create a calculated column
CalcCats = var _black=IF ( ISBLANK ( SEARCH ( "Black", Table1[Data], 1, BLANK () ) ), 0,1) var _big=IF ( ISBLANK ( SEARCH ( "big", Table1[Data], 1, BLANK () ) ), 0,1) var _cats=IF ( ISBLANK ( SEARCH ( "cats", Table1[Data], 1, BLANK () ) ), 0,1) return _black+_big+_cats
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Thanks so much for your help.
You can use this calculated column
See attached file as well
Calculated Column = SUMX ( ADDCOLUMNS ( Words, "Number of Matches", IF ( SEARCH ( [Words], [Sentence], 1, 0 ) > 0, 1 ) ), [Number of Matches] )
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |