Forum Discussion

FP68's avatar
FP68
Helper I
6 months ago
Solved

Problem with calcul

Hello the community,   I try to extract a chain of caracters from a columns and I don't understand why I've the message "an argument of function MID has the wrong data type or has an invalid value"...
  • FBergamaschi's avatar
    FBergamaschi
    6 months ago

    Hi FP68 

    here the correct code

     

    ApplicationName =
    VAR TagText = Disques_Global[TAGS]
    VAR _Key = "ApplicationName="
    VAR KeyPos = SEARCH ( _Key, TagText, 1, 0 )
    VAR ValueStart = KeyPos + LEN ( Key )
    VAR SemiPos = SEARCH ( ";", TagText, ValueStart, 0 )
    RETURN
    IF (
        KeyPos = 0,
        BLANK(),
        IF (
            SemiPos = 0,
            MID ( TagText, ValueStart, LEN ( TagText ) - ValueStart + 1 ),
            MID ( TagText, ValueStart, SemiPos - ValueStart )
        )
    )
     

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

xifeng_L's avatar
xifeng_L
Super User
6 months ago

Hi FP68 

 

Regarding your scenario of extracting strings, you can use the following DAX UDF:

 

DEFINE
    /// Returns the text between specified delimiters from a string. The startIndex parameter can be used to specify which starting delimiter to use, while the endIndex parameter specifies which ending delimiter to use. However, the starting position of endIndex is relative to the position after startIndex.
    FUNCTION XF.Str.BetweenDelimiters = (str:string,startDelimiter:string,endDelimiter:string,startIndex:int64,endIndex:int64) =>
    IF(startIndex<1 || endIndex<1,
        ERROR("startIndex and endIndex should >=1"),
        VAR StartDelimiterIndex = 
            DISTINCT(
                FILTER(
                    SELECTCOLUMNS(
                        GENERATESERIES(1,LEN(str)),
                        "Position",FIND(startDelimiter,str,[Value],BLANK())
                    ),
                    [Position]<>BLANK()
                )
            )
        VAR StartDelimiterIndex_AddRank = 
            ADDCOLUMNS(
                StartDelimiterIndex,
                "Rank",RANKX(StartDelimiterIndex,[Position],,1)
            )
        VAR StartIndex = COALESCE(MAXX(FILTER(StartDelimiterIndex_AddRank,[Rank]=startIndex),[Position]),LEN(str))
        VAR RightText = RIGHT(str,MAX(LEN(str)-(StartIndex+LEN(startDelimiter)-1),0))
		VAR SplitedKey = "虪"
		VAR Alter_RightText = SUBSTITUTE(RightText,"|",SplitedKey)
		VAR Alter_EndDelimiter = SUBSTITUTE(endDelimiter,"|",SplitedKey)
        VAR TransToPath = SUBSTITUTE(Alter_RightText,Alter_EndDelimiter,"|")
        RETURN
        CONCATENATEX(
            ADDCOLUMNS(
                GENERATESERIES(1,MIN(endIndex,PATHLENGTH(TransToPath))),
                "SubStr",SUBSTITUTE(PATHITEM(TransToPath,[Value]),SplitedKey,"|")
            ),
            [SubStr],
            endDelimiter,
            [Value],ASC
        )
    )

 

For example:

 

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~