An argument of the MID has the wrong data type or has an invalid data value

[lisawinck]

I am getting the above error when trying to combine the MID function with the SEARCH function. Training Type = trim(MID(Competency[Survey Question],iferror(SEARCH(":",Competency[Survey Question],,0),0),iferror(SEARCH("-",Competency[Survey Question],,0),0))) The search functions work fine when using them alone without the MID function. SEARCH(":",Competency[Survey Question],,0) SEARCH("-",Competency[Survey Question],,0) When I try to use them as start and stop I get the error. If I replace the first search with a numerical number, it runs without error. Does anyone know if this is a known issue or have any suggestions? Thanks!

[v-jiascu-msft · Accepted solution]

Hi @lisawinck,

If a Competency[Survey Question] doesn't have a ":", the second parameter of "MID" will be 0, which is illegal. I would suggest making some changes of the formula.

Training Type = 
TRIM (
    MID (
        Competency[Survey Question],
        IFERROR ( SEARCH ( ":", Competency[Survey Question],, 1 ), 1 ),
        IFERROR ( SEARCH ( "-", Competency[Survey Question],, 0 ), 0 )
    )
)

BTW, it would be a good idea to format the formula with: http://www.daxformatter.com/

Best Regards!

Dale

[lisawinck]

Also, that's a cool tool.  Thanks!

[lisawinck]

That worked perfectly!  Thank you!!  I thought the zero was the correct method.  Thanks for your help!