Forum Discussion

agrandi's avatar
agrandi
Frequent Visitor
2 years ago
Solved

Extract Text from value

I'm trying to extract a text from a values of column  issues_comments[comment_body]:

comment =
var _value0 = issues_comments[comment_body]
var _value1 = FIND(", ""id"": ", _value0, 1, 0)
var _value2 = IF(_value1 = 0, 0, MID(_value0, INT(_value1)+9, 12))
return IF(ISBLANK(_value2), 0, _value2)

 

the formula MID(_value0, INT(_value1)+9, 12) generate the follow error:

"Expressions that yield variant data-type cannot be used to define calculated columns."

 

Maybe I'm trying to use DAX like Excel, put I need to identify the text needed it order to substitute it with another value.

Do you have suggestions?

 

Thanks in advance

 

AG

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi agrandi ,

    Please try the following methods and check if they can solve your problem:

    1.Create the simple table.

    2.Create the new measure to extract value.

     

    comment = 
VAR _value0 = SELECTEDVALUE(issues_comments[comment_body])
VAR _value1 = FIND(", ""id"": ", _value0, 1, 0)
VAR _substring = MID(_value0, LEN(_value1) + 61, 13)
//VAR _substring = RIGHT(_value0, LEN(_value0) - (_value1 + 60))
RETURN IF(ISBLANK(_substring), 0, _substring)

     

     

    3.Drag the measure into the table visual.

     

    4.Select the column value. The result is shown below.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    You better show me what you have and what you want to get
    you need to post an example and the result you want to get

  • agrandi's avatar
    agrandi
    Frequent Visitor
    2 years ago

    This is one example of values that I need to manipulate:
    "@{""type"": ""user"", ""id"": ""XXXXXXW43XHG"", ""name"": ""Xxxxx Yyyyyyy""}, I'm able to see them but why move comments to another form? There is nothing that advice the user that comments are made. Just opening the comment icon, they can see if comments are present or not. Maybe some extra implementations are needed."

    So, I need to read the two parameters on these brackets that are dinamic and will continue to change:
    @{""type"": ""user"", ""id"": ""XXXXXXW43XHG"", ""name"": ""Xxxxx Yyyyyyy""}

    So, with my formula I just started to identify the ID... and later I will extract the name also.

    The final result shall be:
    "@Xxxxx Yyyyyyy, I'm able to see them but why move comments to another form? There is nothing that advice the user that comments are made. Just opening the comment icon, they can see if comments are present or not. Maybe some extra implementations are needed."

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi agrandi ,

    Please try the following methods and check if they can solve your problem:

    1.Create the simple table.

    2.Create the new measure to extract value.

     

    comment = 
VAR _value0 = SELECTEDVALUE(issues_comments[comment_body])
VAR _value1 = FIND(", ""id"": ", _value0, 1, 0)
VAR _substring = MID(_value0, LEN(_value1) + 61, 13)
//VAR _substring = RIGHT(_value0, LEN(_value0) - (_value1 + 60))
RETURN IF(ISBLANK(_substring), 0, _substring)

     

     

    3.Drag the measure into the table visual.

     

    4.Select the column value. The result is shown below.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.