{"id":1100,"date":"2014-01-27T22:24:15","date_gmt":"2014-01-28T05:24:15","guid":{"rendered":"https:\/\/mmman.itgroove.net\/?p=1100"},"modified":"2023-02-24T21:48:35","modified_gmt":"2023-02-24T21:48:35","slug":"practical-use-of-the-dax-search-function","status":"publish","type":"post","link":"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/","title":{"rendered":"Practical Use of the DAX Search Function"},"content":{"rendered":"

I\u2019ve found that when using the DAX search function that I often need to include the two optional parameters (\u201cstart num\u201d and \u201cnot found value\u201d).  <\/p>\n

SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])<\/pre>\n
I\u2019ll explain the reasoning behind my statement.  What I\u2019ve found when not using the optional parameters, if that if you have no matching value from your search, you end up with an \u201c#ERROR\u201d cell in Excel, and that just makes the field \u201cdirty\u201d with lots of \u201cerror\u201d cells hanging around (and often causes issues with using the column later on).<\/p>\n
For example the following formula<\/p>\n
=SEARCH(“BOB”,[Client])<\/font><\/p>\n
when searching on the data below returns these results<\/p>\n\n\n\n\n\n\n\n\n
Source<\/strong><\/td>\nResult<\/strong><\/td>\n<\/tr>\n
BOBBY<\/td>\n1<\/td>\n<\/tr>\n
SUE<\/td>\n#ERROR<\/td>\n<\/tr>\n
JOE<\/td>\n#ERROR<\/td>\n<\/tr>\n
SAM<\/td>\n#ERROR<\/td>\n<\/tr>\n
SALLY<\/td>\n#ERROR<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
However, when using the optional parameters<\/p>\n
=SEARCH(“BOB”,[Client], 1, 0)<\/font><\/p>\n
when searching on the same data returns these (much more easily digestible) results<\/p>\n\n\n\n\n\n\n\n\n
Source<\/strong><\/td>\nResult<\/strong><\/td>\n<\/tr>\n
BOBBY<\/td>\n1<\/td>\n<\/tr>\n
SUE<\/td>\n0<\/td>\n<\/tr>\n
JOE<\/td>\n0<\/td>\n<\/tr>\n
SAM<\/td>\n0<\/td>\n<\/tr>\n
SALLY<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Alternatively, you can use this formula below without the optional parameters, but I prefer to account for my errors as much as possible within the given statement, instead of catching them outside a statement.<\/p>\n
=IFERROR(SEARCH(“BOB”,[Client]), 0)<\/font><\/p>\n
Which will return the same results as the second table above.<\/p>\n
 <\/p>\n
Note, don\u2019t confuse the DAX Search function<\/a> this with the Excel Search function<\/a>.  The two are similar, but have different parameters, and are intended to work in different places (one works within the PowerPivot window in Excel, the other is designed to work outside the PowerPivot window in Excel.<\/p>\n","protected":false},"excerpt":{"rendered":"
I\u2019ve found that when using the DAX search function that I often need to include the two optional parameters (\u201cstart num\u201d and \u201cnot found value\u201d).  SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) I\u2019ll explain the reasoning behind my statement.  What I\u2019ve found when not using the optional parameters, if that if you have no matching value from your … <\/a><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[21,30],"tags":[],"acf":[],"yoast_head":"\nPractical Use of the DAX Search Function - Archive<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Practical Use of the DAX Search Function - Archive\" \/>\n<meta property=\"og:description\" content=\"I\u2019ve found that when using the DAX search function that I often need to include the two optional parameters (\u201cstart num\u201d and \u201cnot found value\u201d).  SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) I\u2019ll explain the reasoning behind my statement.  What I\u2019ve found when not using the optional parameters, if that if you have no matching value from your …\" \/>\n<meta property=\"og:url\" content=\"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/\" \/>\n<meta property=\"og:site_name\" content=\"Archive\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-28T05:24:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-24T21:48:35+00:00\" \/>\n<meta name=\"author\" content=\"Colin Phillips (Alumni)\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Colin Phillips (Alumni)\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/\",\"url\":\"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/\",\"name\":\"Practical Use of the DAX Search Function - Archive\",\"isPartOf\":{\"@id\":\"https:\/\/regroove.ca\/archive\/#website\"},\"datePublished\":\"2014-01-28T05:24:15+00:00\",\"dateModified\":\"2023-02-24T21:48:35+00:00\",\"author\":{\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b\"},\"breadcrumb\":{\"@id\":\"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Blog Archive\",\"item\":\"https:\/\/regroove.ca\/archive\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Practical Use of the DAX Search Function\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/regroove.ca\/archive\/#website\",\"url\":\"https:\/\/regroove.ca\/archive\/\",\"name\":\"Archive\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/regroove.ca\/archive\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b\",\"name\":\"Colin Phillips (Alumni)\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g\",\"caption\":\"Colin Phillips (Alumni)\"},\"url\":\"https:\/\/regroove.ca\/archive\/author\/cphillips\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Practical Use of the DAX Search Function - Archive","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/","og_locale":"en_US","og_type":"article","og_title":"Practical Use of the DAX Search Function - Archive","og_description":"I\u2019ve found that when using the DAX search function that I often need to include the two optional parameters (\u201cstart num\u201d and \u201cnot found value\u201d).  SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) I\u2019ll explain the reasoning behind my statement.  What I\u2019ve found when not using the optional parameters, if that if you have no matching value from your …","og_url":"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/","og_site_name":"Archive","article_published_time":"2014-01-28T05:24:15+00:00","article_modified_time":"2023-02-24T21:48:35+00:00","author":"Colin Phillips (Alumni)","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Colin Phillips (Alumni)","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/","url":"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/","name":"Practical Use of the DAX Search Function - Archive","isPartOf":{"@id":"https:\/\/regroove.ca\/archive\/#website"},"datePublished":"2014-01-28T05:24:15+00:00","dateModified":"2023-02-24T21:48:35+00:00","author":{"@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b"},"breadcrumb":{"@id":"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/regroove.ca\/archive\/2014\/01\/27\/practical-use-of-the-dax-search-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Archive","item":"https:\/\/regroove.ca\/archive\/"},{"@type":"ListItem","position":2,"name":"Practical Use of the DAX Search Function"}]},{"@type":"WebSite","@id":"https:\/\/regroove.ca\/archive\/#website","url":"https:\/\/regroove.ca\/archive\/","name":"Archive","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/regroove.ca\/archive\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b","name":"Colin Phillips (Alumni)","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g","caption":"Colin Phillips (Alumni)"},"url":"https:\/\/regroove.ca\/archive\/author\/cphillips\/"}]}},"_links":{"self":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/1100"}],"collection":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/comments?post=1100"}],"version-history":[{"count":1,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/1100\/revisions"}],"predecessor-version":[{"id":1890,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/1100\/revisions\/1890"}],"wp:attachment":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/media?parent=1100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/categories?post=1100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/tags?post=1100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}