Problem with calcul

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~

hi FP68 ,

In "_y = Disques_Global[Countries2]  - _x", could _y be negative?
Negative arguments in MID could lead to such error.

Hello FP68, 

Your error comes from subtracting the wrong positions, which sometimes gave MID a zero or negative length. The corrected formula above works in both Power BI and Microsoft Fabric semantic models.

Correct DAX Formula: 

ApplicationName = VAR StartPos = SEARCH("ApplicationName=", [TAGS], 1, 0) + LEN("ApplicationName=") VAR EndPos = SEARCH(";", [TAGS], StartPos, 0) VAR Length = EndPos - StartPos RETURN MID([TAGS], StartPos, Length)

Why this fixes the error

• SEARCH("ApplicationName=", [TAGS]) finds the keyword.
• + LEN("ApplicationName=") moves the pointer to the first character after the equals sign.
• SEARCH(";", [TAGS], StartPos) finds the next semicolon.
• EndPos - StartPos guarantees a positive length for MID.
• MID([TAGS], StartPos, Length) extracts the substring correctly.

The original error happened because _y = Countries2 - _x sometimes produced zero or negative values, which MID cannot accept.

Works in Both Power BI and Microsoft Fabric

• Power BI Desktop: Use this formula in a calculated column or measure.

Microsoft Fabric (Lakehouse / Data Warehouse): When you build a semantic model in Fabric, you use the same DAX functions. This formula works identically because Fabric’s semantic models run on the same DAX engine as Power BI.

Official Microsoft Documentation

MID FUNCTION DAX 
Serach Function 
FIND Function 


Generalization Tip
If you want to extract any key=value pair from TAGS, you can parameterize the key:

ExtractValue = VAR Key = "ApplicationName=" VAR StartPos = SEARCH(Key, [TAGS], 1, 0) + LEN(Key) VAR EndPos = SEARCH(";", [TAGS], StartPos, 0) VAR Length = EndPos - StartPos RETURN MID([TAGS], StartPos, Length)
Change Key to "UserName=", "Version=", etc., and reuse the same logic.

Hello FP68,

anyway you can share your pbix via private message to me so I fix the issue? I see a lot of messages and I would like to cut time to the solution.

Best

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

Please try the following formula:

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 )
    )
)

Hi FP68 ,

This error is happening because 1 or more of your rows does not have "ApplicationName", "ApplicationName=" or ";". This is then returning 0 in you '_x' and or '_y' variables (which MID does not accept as either the starting position or length).

To account for this in you calculated column, simplle update the DAX for the column ApplicationName to the below, this will allow for the missing data. You could then filter on this column to see where the ApplicationName=blank, this will then show the TAGS that are not conforming to the expected format.

VAR _x =
    FIND ( "ApplicationName", [TAGS], 1, 0 )
VAR _y = [Countries2] - _x
VAR _XErrorFix =
    IF ( _x = 0, 1, _x )
VAR _YErrorFix =
    IF ( _y = 0, 1, _y )
VAR _z =
    MID ( [TAGS], _XErrorFix, _YErrorFix )
RETURN
    IF ( _x = 0 || _y = 0, BLANK (), _z )

Hope this helps, AWD

If it did help, please ✓ Mark as Solution, it helps other find this post. 

Kudos appreciated 🙂