Problem with calcul

[FP68]

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"

My column TAGS is in text format and contains ".......;ApplicationName=.......;....."

I put:

ApplicationName =

var _x = find("ApplicationName",[TAGS],1,0)

var _y = Disques_Global[Countries2]  - _x

var _z = MID (Disques_Global[Tags], _x,_y)

return _z

and 

Countries2 =

VAR _x =     SEARCH ( "ApplicationName=" , [TAGS] , 1 , 0 )

VAR _z =     _x + LEN ( "ApplicationName=" )

VAR SemiColonPos =     SEARCH ( ";" , [TAGS] , _z , 0 )

RETURN  SemiColonPos  

Thanks a lot for your help

[FBergamaschi · Accepted solution]

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

[FP68]

Thanks a lot Frnacesco and thanks to all

[AWD]

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 🙂

[cengizhanarslan]

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

[xifeng_L]

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~

[FBergamaschi]

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

[Olufemi7]

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.

[FP68]

Hi @Olufemi7,
Thanks a lot for your explanation and proposal of solution but I've always the same message:

Is it due because sometime the [TAGS] finished by the "ApplicationName..." and we haven't a ";" ?

[Olufemi7]

Hi @FP68,

Great catch yes, that’s exactly the issue. When [TAGS] ends with "ApplicationName=..." and there’s no trailing semicolon, the original formula fails because SEARCH(";", ...) returns 0, which causes MID to break.
I wanted to share a full walkthrough of how I implemented this in Power BI Service using a semantic model.

Semantic Model Setup in Power BI Service

I created calculated columns directly in the semantic model using the following logic:

ApplicationName

ApplicationName = 
VAR SourceText =
    COALESCE([Column1], [Column2], [Column3], [TAGS])
RETURN
MID(
    SourceText & ";",
    SEARCH("ApplicationName=", SourceText, 1) + LEN("ApplicationName="),
    SEARCH(";", SourceText & ";", SEARCH("ApplicationName=", SourceText, 1) + LEN("ApplicationName=")) -
    (SEARCH("ApplicationName=", SourceText, 1) + LEN("ApplicationName="))
)

ApplicationId

ApplicationId = 
IFERROR(
    MID(
        [TAGS] & ";",
        SEARCH("ApplicationId=", [TAGS], 1) + LEN("ApplicationId="),
        SEARCH(";", [TAGS] & ";", SEARCH("ApplicationId=", [TAGS], 1) + LEN("ApplicationId=")) -
        (SEARCH("ApplicationId=", [TAGS], 1) + LEN("ApplicationId="))
    ),
    BLANK()
)

Countries

Countries = 
IFERROR(
    MID(
        [Column2] & ";",
        SEARCH("Countries=", [Column2], 1) + LEN("Countries="),
        SEARCH(";", [Column2] & ";", SEARCH("Countries=", [Column2], 1) + LEN("Countries=")) -
        (SEARCH("Countries=", [Column2], 1) + LEN("Countries="))
    ),
    BLANK()
)

Environment

Environment = 
IFERROR(
    MID(
        [Column3] & ";",
        SEARCH("Environment=", [Column3], 1) + LEN("Environment="),
        SEARCH(";", [Column3] & ";", SEARCH("Environment=", [Column3], 1) + LEN("Environment=")) -
        (SEARCH("Environment=", [Column3], 1) + LEN("Environment="))
    ),
    BLANK()
)

SampleData_TAGS	SampleData_Column1	SampleData_Column2	SampleData_Column3	SampleData_ApplicationName	SampleData_Environment	SampleData_Countries	SampleData_ApplicationId
ApplicationId=fsp	ApplicationName=fileserv/pfne		fileserv/pfne			fsp
ApplicationId=fsp	ApplicationName=fileserv/pfp	Countries=pl	Environment=prod	fileserv/pfp	prod	pl	fsp
Name=yyal02h0	ApplicationName=sauve/netbackup	Environment=prod		sauve/netbackup

Why This Works

Yes — as you pointed out — the original error happens when [TAGS] ends with "ApplicationName=..." and there's no trailing semicolon. That causes SEARCH(";", ...) to return 0, which breaks MID.

By appending ";" to the source string, the formula always finds a delimiter, even if the original string doesn't end with one. That’s the key fix.

This approach works across both Power BI Desktop and Microsoft Fabric, since the semantic model uses the same DAX engine. You can easily adapt the logic to extract any key=value pair from any column.

Let me know if you'd like help building a reusable function or Power Query version  happy to share that too!

[FreemanZ]

hi @FP68 ,

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

[FP68]

Hi
No _y couldn't be negative

[FreemanZ]

in Countries2, should it be like:
SemiColonPos = SEARCH ( ";" , [TAGS] , _z , 0 ) + _z

[FP68]

No SemiColonPos is the posiion of ";" after the word ApplicationName

[FreemanZ]

with ".......;ApplicationName=.......;.....", could you confirm the comments in red:

Countries2 =
VAR _x = SEARCH ( "ApplicationName=" , [TAGS] , 1 , 0 )
VAR _z = _x + LEN ( "ApplicationName=" )
VAR SemiColonPos = SEARCH ( ";" , [TAGS] , _z , 0 )
RETURN SemiColonPos  //returns 8?

ApplicationName =
var _x = find("ApplicationName",[TAGS],1,0) //_x returns 9?
var _y = Disques_Global[Countries2] - _x  //_y return -1?
var _z = MID (Disques_Global[Tags], _x,_y)
return _z

[FP68]

Countries2 = 32 ==> position of ";" after "ApplicationName"

_x = 9 ==> position of "ApplicationName"

_y = 32 -9 = 23 ==> length of the chain to extract

[FreemanZ]

i see. with your code and sample data, i see no error:

could you post more sample data?

[FP68]

[FreemanZ]

hi @FP68 ,

Seemingly in the first 3 rows, "var _x = find("ApplicationName",[TAGS],1,0)" will return 0, which leads to error in MID.