+ Reply to Thread
Results 1 to 7 of 7

ISNUMBER SEARCH in Data Model Calculated Column

  1. #1
    Registered User
    Join Date
    10-28-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2013
    Posts
    8

    Question ISNUMBER SEARCH in Data Model Calculated Column

    Hello All -
    I am trying to add a calculated column in my Data Model to search for a text match within a column. I tried
    Please Login or Register  to view this content.
    on a worksheet, and it works fine. However, recreating this in the Data Model, as
    Please Login or Register  to view this content.
    (where Table[Column] is the column to search) returns an error: "The search Text provided to function 'SEARCH' could not be found in the given text." Is this not possible within the DM? Is there another way of approaching this? Note that the text values that I'm looking for are fuzzy searches, that is the value in the search column could be "Kid's Book", or "Paperback Book", etc.

    Thanks!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: ISNUMBER SEARCH in Data Model Calculated Column

    Try this:

    =IF(ISNUMBER(SEARCH("Book",Table[@Column])),TRUE,FALSE)

    If that's not it, then attach a sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    217

    Re: ISNUMBER SEARCH in Data Model Calculated Column

    The syntax is different in DAX - try : =SEARCH("Book",Table[Column],1,BLANK())<>BLANK()

  4. #4
    Registered User
    Join Date
    10-28-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: ISNUMBER SEARCH in Data Model Calculated Column

    Thank you GlennUK - that did the trick!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: ISNUMBER SEARCH in Data Model Calculated Column

    Administrative Note

    Please don't ignore contributors to your thread - acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  6. #6
    Registered User
    Join Date
    10-28-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: ISNUMBER SEARCH in Data Model Calculated Column

    Thank you AliGW.

  7. #7
    Forum Contributor
    Join Date
    11-20-2007
    Location
    Felixstowe, England
    MS-Off Ver
    Excel 2013, 2019
    Posts
    217

    Re: ISNUMBER SEARCH in Data Model Calculated Column

    Quote Originally Posted by a68tbird View Post
    Thank you GlennUK - that did the trick!
    My pleasure. Those DAX differences can be hard to spot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-10-2019, 02:43 PM
  2. [SOLVED] Calculated Field in Pivot from Data Model
    By Gazsim in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-18-2019, 11:44 AM
  3. [SOLVED] Generate a calculated column to capture patient "Readmitted (Y/N)", in a data Model
    By kam77 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-09-2018, 04:50 PM
  4. Excel data model calculated fields
    By Gavinchap in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2017, 05:37 PM
  5. adding a calculated field to power pivot data model
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-26-2016, 07:13 PM
  6. Running Count in PowerPivot
    By efarkouh in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-07-2014, 10:33 AM
  7. Calculated Field When Using Data Model
    By efarkouh in forum Excel General
    Replies: 0
    Last Post: 10-06-2014, 03:57 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1