- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Retrieve the info in value field after first 'Output' string appearance (as highlighted below)
- If 'Output' string is not available (or) value is empty (or) value is nor numeric, return '0' or null.
- 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.
Index | Serial Number | Results |
1 | TST2334 | {"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"","MetricUnit":null,"MeasurementUnit":null}], ....................................... |
2 | DY75677 | {"MeasuredValue":[{"Name":"TestType","Value":"NonMeasurement","MetricUnit":null,"MeasurementUnit":null}], .......................... |
3 | DCQ7869 | {"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"264","MetricUnit":null,"MeasurementUnit":""}, ............................. |
4 | UAE9354 | {"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"88.23","MetricUnit":null,"MeasurementUnit":"dB"}, .................... |
5 | F1R3496 | {"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null}],"InputParameter":[{"Name":"TestFunctionVisibility","Value":"True","MetricUnit":null,"MeasurementUnit":null} |
6 | UAE9377 | {"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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
01-10-2025 07:24 AM | |||
01-24-2025 05:51 AM | |||
09-05-2024 09:40 PM | |||
05-07-2024 04:03 AM | |||
08-24-2018 09:51 AM |
User | Count |
---|---|
87 | |
81 | |
53 | |
38 | |
35 |