Reply
DreamToGet
Frequent Visitor
Partially syndicated - Outbound

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 support forums, I understood the easier way is to use 'Parse JSON' via transform data and retrieve only the needed info but I can't use it as it breaks Query folding for 'Incremental Refresh'.

 

[Note: The entire string in 'Results' column in too large and I have only pasted the relevant part]

In order to achieve this, I am trying to create a new column with DAX query as pasted below. I would like to perform the below operations.

  1. Retrieve the info in value field after first 'Output' string appearance (as highlighted below)
  2. If 'Output' string is not available (or) value is empty (or) value is nor numeric, return '0' or null.
  3. Convert the output to decimal format.

Can you have a look at the DAX query and please let me know what am I doing wrong? Also, is there any other better/efficient way to achieve this? Would appreciate your help on this.

 

IndexSerial NumberResults
1TST2334{"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"","MetricUnit":null,"MeasurementUnit":null}], .......................................
2DY75677{"MeasuredValue":[{"Name":"TestType","Value":"NonMeasurement","MetricUnit":null,"MeasurementUnit":null}], ..........................
3DCQ7869{"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"264","MetricUnit":null,"MeasurementUnit":""}, .............................
4UAE9354{"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"88.23","MetricUnit":null,"MeasurementUnit":"dB"}, ....................
5F1R3496{"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null}],"InputParameter":[{"Name":"TestFunctionVisibility","Value":"True","MetricUnit":null,"MeasurementUnit":null}
6UAE9377{"MeasuredValue":[{"Name":"TestType","Value":"CurveMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"83.64","MetricUnit":null,"MeasurementUnit":............

 

 

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
    IFERROR ( MID ( [Results], VALUE ( FindColon + 2 ), temp ), "null" )

 

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Syndicated - Outbound

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 :

vluwangmsft_0-1629271832763.png

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Syndicated - Outbound

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 :

vluwangmsft_0-1629271832763.png

 

Wish it is helpful for you!

 

Best Regards

Lucien

lbendlin
Super User
Super User

Syndicated - Outbound

uh, you make it unnecessary hard on yourself.  Power Query has a built-in JSON parser. Maybe you can do the transforms there?

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)