locked
Power BI DAX question RRS feed

  • Question

  • This might not be the correct forum for this question but maybe someone knows the answer.  Using DAX in Power BI, I am trying to use 2 search functions to find the start and end points of a text string for the MID function.  Is this not possible or am I doing something wrong?

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

    Training Type = trim(MID(Competency[Survey Question],value(SEARCH(":",Competency[Survey Question],,0)),SEARCH("-",Competency[Survey Question],,0)))

    I get the following error with both syntax:

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

    However, when using each piece separately, they work fine.

    Friday, August 25, 2017 3:04 PM

Answers

  • the Mid function expect a string value, a starting character and the number of characters to return. I looks like you are trying to pass the end position as the third parameter. You would probably have to subtract the position of "-" from the position of ":" for your third parameter (or my preference is to do any sort of string manipulation like this before landing the data in the model if I can)
     

    http://darren.gosbell.com - please mark correct answers

    Saturday, August 26, 2017 12:53 AM

All replies

  • the Mid function expect a string value, a starting character and the number of characters to return. I looks like you are trying to pass the end position as the third parameter. You would probably have to subtract the position of "-" from the position of ":" for your third parameter (or my preference is to do any sort of string manipulation like this before landing the data in the model if I can)
     

    http://darren.gosbell.com - please mark correct answers

    Saturday, August 26, 2017 12:53 AM
  • Hi MSquerier,

    Thanks for your question.

    Firstly, you may need to create a calculated column, not a measure. 

    Second, as Darren said, the third parameter is the number of characters you want to return.

    See my sample DAX below:

    Desired =
    TRIM (
        MID (
            Competency[Survey Question],
            SEARCH ( ":", Competency[Survey Question],, 0 ) + 1,
            SEARCH ( "-", Competency[Survey Question],, 0 )
                - SEARCH ( ":", Competency[Survey Question],, 0 )
                - 1
        )
    )

    According to your description, your problem is more related to PowerBI. Since our forum is discussing SQL Server Analysis Services issue, to solve your question more efficiently, please post your question in PowerBI forum: http://community.powerbi.com ,you will get a more professional support from there, thank you for your understanding and support.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 28, 2017 7:04 AM
  • Hi,

    It will help if you show some data and the expected result.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, September 1, 2017 3:12 AM