Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Need to extract a certain set of information from a text field

    Posted Aug 15, 2022 11:17 PM
    I have a filed assigned_to
    field is name (networkid)  i.e.  John Smith (smith01)

    I need just the smith01

    I tried this

    MID(incidents_av[assigned_to_name],SEARCH("(",incidents_av[assigned_to_name],,-1)+1,7)

    but the search function gives an error

    by it self this works fine
    SEARCH("(",incidents_av[assigned_to_name],,-1)    (it returns 12 which is correct)

    but if I include it in the MID function it gives an error message

    not sure what I am missing.

    any help is appreciated

    ------------------------------
    Randy Etheridge
    Veritiv Corporation
    P&F USA Inc
    Torrance CA
    770-315-9670
    ------------------------------


  • 2.  RE: Need to extract a certain set of information from a text field

    Posted Aug 16, 2022 02:50 PM
    Have you tried just replacing the "SEARCH" function in  the MID with the number 12?  If the MID still has an error, then you would know it's not the SEARCH that's the problem, but something else in the MID.

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------



  • 3.  RE: Need to extract a certain set of information from a text field

    Bronze Contributor
    Posted Aug 18, 2022 03:24 AM
    Your function looks okay; I suspect DAX is telling you that SEARCH fails on a row of data - do you have any rows without a "(" in it ?

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 4.  RE: Need to extract a certain set of information from a text field

    Posted Aug 18, 2022 09:34 AM

    James I suspect you are correct.  there are 600,00+ rows, and I would not expect any missing data in the field I am using,  assigned_to_name.

    However I just learned last night that when an employee leaves the company, they change the employees id.  AND that the software does not "hold" the original data, but is actually a connection to a name table so the field assigned_to_name is then updated for all prior records.

    And the updated name for the now gone employee does not contain a "(" 

    I am testing this today and will update this thread when I know the answer.



    ------------------------------
    Randy Etheridge
    Veritiv Corporation
    P&F USA Inc
    Torrance CA
    770-315-9670
    ------------------------------