Parse a column with Json data using DAX?

[Yiyi_1989]

Hello, 

Has someone experienced in parse a column with Json data using Dax? I have datasets which are connected to my PowerBI desktop using LiveConnection, so Power Query is not a option for me :(. 

My value has such structure as {"id":"12345","name":"Lily","keywords":"null","filters":"[]"} and I want to create a new column with name as column name and the value after name as the column values. 

Is it possible to do so using DAX? 

Thanks a lot for your help in advance!

<3<3<3

Brenda

[rsbin]

@Yiyi_1989 ,

DAX has a number of string functions that can be used to parse a text string.

Here is one method.  I have broken it out into separate columns to better see the logic.
You can combine it into one if you are comfortable with that.

FindStart = FIND("name", [JSON] ) + 7  // Find your starting position for the Name

FindEnd = FIND( "keywords", [JSON] ) - 3  // Finds your ending position

Name = MID( [JSON], [FindStart], [FindEnd] - [FindStart] )  // Extracts the Name

A google search on DAX string functions may lead to other methods.  As I said, this is simply one way.

Hope this helps.

Regards,

[Yiyi_1989]

Thanks a lot! I think your suggestion is inspiring. 

However, I do have an issue regarding the values in [Json]. Those values have basically two different structures:  

One started as {"searchID":"... ... ..." ... } and another one is like the one I mentioned above {"id":"12345","name":"Lily","keywords":"null","filters":"[]"}.

I want to specify in my DAX that the FIND() function only applied to the second structure type. Do you have any idea about what should I do? Thanks a lot!

[rsbin]

@Yiyi_1989 ,

Use a SWITCH or IF Statement.   In DAX, I much prefer using SWITCH.  So modify the [Name] Column to something like this:

Name2 = SWITCH(
           TRUE(),
           MID( [JSON], 3, 2 ) = "id", MID( [JSON], [FindStart], [FindEnd] - [FindStart] ),
                                      // this checks for the condition = "id"
           BLANK() ) // if condition <> "id", then blank

You can replace BLANK() with whatever you want your "else" part to be.

Hope this resolves this issue.

Regards,