PowerPivot - Dax Find Text in another Cell - Cannot get string value

0
votes

I have what is simple data and I want to return the Home or Motor if a find search returns True.

For Example

Skills          Type
I Home Sr   
A Mot Pre

Type is my custom column

Starting with just returning True and it fails right here with

=FIND("Home",[Skills])

With

Calculation error in column 'Table1'[]: The search Text provided to function 'FIND' could not be found in the given text.

Ultimately I want to use If Find is "Home" Return Home if "Motor" return Motor

Desired Output (please note there are other starting variations to the Skills so cannot use a fixed search point in text)

  Skills          Type
I Home Sr         Home
A Mot Pre         Motor
1
powerpivotdax
It just doesn't seem to see the text of the cell I am referencing – sayth
Any occurrence of Mot, Moto or Motor should produce Motor in the Type column, right?. – alejandro zuleta
Yes any match on mot is good or hom for home – sayth

1 Answers

1
votes

Use the following expression for Type column:

=
IF (
    IFERROR ( SEARCH ( "Mot*", [Skills], 1, 0 ), 0 ),
    "Motor",
    IF ( IFERROR ( SEARCH ( "Hom*", [Skills], 1, 0 ), 0 ), "Home", "Nothing" )
)

It will generate Home or Motor for any occurrence of Hom* and Mot*, note the * wildcard

It should produce:

enter image description here

The screenshot shows the table generated in Power BI, but this solution works in PowerPivot too, I don't have access to PowerPivot in this moment.

Note if any occurrence is not found it will put "Nothing" in your Type column so you can replace "Nothing" by the string you want to appear in that case.

SEARCH function documentation can be read here.

Let me know if this helps.