Forum Discussion

Yiyi_1989's avatar
Yiyi_1989
Icon for Helper I rankHelper I
2 years ago

Parse a column with Json data using DAX?

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 :...
Need Help
Yiyi_1989's avatar
Yiyi_1989
Icon for Helper I rankHelper I
2 years ago

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's avatar
rsbin
Icon for Community Champion rankCommunity Champion
2 years ago

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,