topic Re: Problem with calcul in DAX Commands and Tips

Problem with calcul

FP68 — Tue, 27 Jan 2026 06:16:43 GMT

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

Re: Problem with calcul

FreemanZ — Tue, 27 Jan 2026 06:43:03 GMT

hi FP68 ,

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

Re: Problem with calcul

FP68 — Tue, 27 Jan 2026 06:47:38 GMT

Hi
No _y couldn't be negative

Re: Problem with calcul

FreemanZ — Tue, 27 Jan 2026 06:49:32 GMT

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

Re: Problem with calcul

FP68 — Tue, 27 Jan 2026 06:52:39 GMT

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

Re: Problem with calcul

FreemanZ — Tue, 27 Jan 2026 07:03:12 GMT

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

Re: Problem with calcul

FP68 — Tue, 27 Jan 2026 07:09:59 GMT

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

_x = 9 ==> position of "ApplicationName"

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

Re: Problem with calcul

FreemanZ — Tue, 27 Jan 2026 07:29:28 GMT

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

could you post more sample data?

Re: Problem with calcul

FP68 — Tue, 27 Jan 2026 07:33:02 GMT

Re: Problem with calcul

Olufemi7 — Tue, 27 Jan 2026 07:39:16 GMT

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.

Re: Problem with calcul

FP68 — Tue, 27 Jan 2026 07:49:41 GMT

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 ";" ?

Re: Problem with calcul

FreemanZ — Tue, 27 Jan 2026 07:50:03 GMT

hi FP68 ,

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

Re: Problem with calcul

FBergamaschi — Tue, 27 Jan 2026 09:01:46 GMT

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

Re: Problem with calcul

Olufemi7 — Tue, 27 Jan 2026 10:12:49 GMT

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!

Re: Problem with calcul

xifeng_L — Tue, 27 Jan 2026 10:22:23 GMT

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~

Re: Problem with calcul

cengizhanarslan — Tue, 27 Jan 2026 10:50:59 GMT

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

Re: Problem with calcul

AWD — Tue, 27 Jan 2026 12:03:52 GMT

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 🙂

Re: Problem with calcul

FBergamaschi — Tue, 27 Jan 2026 15:20:05 GMT

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

Re: Problem with calcul

FP68 — Wed, 28 Jan 2026 05:46:14 GMT

Thanks a lot Frnacesco and thanks to all