Forum Discussion

FP68's avatar
FP68
Helper I
7 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
    7 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

Olufemi7's avatar
Olufemi7
Super User
7 months ago

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's avatar
    FP68
    Helper I
    7 months ago

    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's avatar
      Olufemi7
      Super User
      7 months ago

      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_TAGSSampleData_Column1SampleData_Column2SampleData_Column3SampleData_ApplicationNameSampleData_EnvironmentSampleData_CountriesSampleData_ApplicationId
      ApplicationId=fspApplicationName=fileserv/pfne fileserv/pfne  fsp 
      ApplicationId=fspApplicationName=fileserv/pfpCountries=plEnvironment=prodfileserv/pfpprodplfsp 
      Name=yyal02h0ApplicationName=sauve/netbackupEnvironment=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!