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

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