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

AWD's avatar
AWD
New Member
7 months ago

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 🙂