Forum Discussion

DreamToGet's avatar
DreamToGet
Frequent Visitor
5 years ago
Solved

JSON/Text Parsing in DAX

Hi, I have a column 'Results' with information stored as string (in JSON format) and would like to parse this string and get only necessary information from this column. After reading through suppor...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi DreamToGet ,

    Try to adjust your measure to below:

     

    Output = 
VAR FindOutput =
    IFERROR ( SEARCH ( "Output", [Results],, 1 ), 1 )
VAR FindValue =
    IFERROR ( SEARCH ( "Value", [Results], FindOutput, 1 ), 1 )
VAR FindColon =
    IFERROR ( SEARCH ( ":", [Results], FindValue, 1 ), 1 )
VAR FindComma =
    IFERROR ( SEARCH ( ",", [Results], FindColon, 1 ), 1 )
VAR temp =
    VALUE ( FindComma - FindColon - 3 )
RETURN
    IF (
        IFERROR ( INT ( MID ( [Results], VALUE ( FindColon + 2 ), temp ) ), BLANK () )
            <> BLANK (),
        FORMAT ( MID ( [Results], VALUE ( FindColon + 2 ), temp ), "Fixed" ),
        BLANK ()
    )

     

    Final get :

     

    Wish it is helpful for you!

     

    Best Regards

    Lucien

v-luwang-msft's avatar
v-luwang-msft
Community Support
5 years ago

Hi DreamToGet ,

Try to adjust your measure to below:

 

Output = 
VAR FindOutput =
    IFERROR ( SEARCH ( "Output", [Results],, 1 ), 1 )
VAR FindValue =
    IFERROR ( SEARCH ( "Value", [Results], FindOutput, 1 ), 1 )
VAR FindColon =
    IFERROR ( SEARCH ( ":", [Results], FindValue, 1 ), 1 )
VAR FindComma =
    IFERROR ( SEARCH ( ",", [Results], FindColon, 1 ), 1 )
VAR temp =
    VALUE ( FindComma - FindColon - 3 )
RETURN
    IF (
        IFERROR ( INT ( MID ( [Results], VALUE ( FindColon + 2 ), temp ) ), BLANK () )
            <> BLANK (),
        FORMAT ( MID ( [Results], VALUE ( FindColon + 2 ), temp ), "Fixed" ),
        BLANK ()
    )

 

Final get :

 

Wish it is helpful for you!

 

Best Regards

Lucien