Home » DAX » DAX SEARCH Function

DAX SEARCH Function

DAX Search function

SEARCH DAX function returns the number of the character at which a specific character or text string is first found, reading left to right. Search is case-insensitive and accent sensitive.

It’s comes under TEXT functions DAX category.

Syntax:

SEARCH(<find_text>, <within_text>, <start_num>, <NotFoundValue>)




Parameters:

find_text: The text you want to find, use double quote.

  • You can use wildcard characters – the question mark (?) and asterisk (*) – in find_text.  A question mark matches any single character, an asterisk matches any sequence of characters.
  • If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

within_text: The text in which you want to search for find_text, or a column containing text.

start_num: (optional) The character position in within_text at which you want to start searching. If omitted, 1.

NotFoundValue: (optional) The value that should be returned when the operation does not find a matching substring, typically 0, -1, or BLANK().

Note:

  • The search function is case insensitive.
  • This function does not support Direct Query mode
  • This function support wildcards.




Let’s get started:

Import below sample dataset into Power BI desktop-

Id Statement Rating
1 Service 5
2 Food Quality 5
3 Atmosphere 7
4 Value for money 5
5 Cleanness 7
6 Coffe Quality 6
7 Help 24 * 7 8
8 AC / Lights 8
9 Help – Desk 10

Now follow these steps-

Step-1: Add one new column , Right click on dataset and select “New Column” and write below DAX function.

Search char position = SEARCH("R",Survey[Statement], , -1)





Step-2: Here we want to find “R” character position for all values of Statement column.

Now click on Data tab and you can see the position number. It will return -1, if not found character in string.

Search DAX function Power BI

Search DAX function Power BI

Step-3: Support wildcard character, only for question mark(?) & asterisk (*) use tilde (~) before the character.

Search DAX function

Search DAX function



Step-4: For other wildcard character- no need to use tilde (~) before the character.

Search DAX with wildcard

Search DAX with wildcard

Refer other DAX post – DAX Tutorials

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.



Leave a Reply