Forum Discussion

SzymonKl's avatar
SzymonKl
Helper I
3 years ago
Solved

undefined

Hi, can you please help me split this column  "CHANGED_FIELDS" into three columns? Unfortunately, I can't do it through the PowerQuery editor because access to data is based on DirectQuery, the...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi SzymonKl ,

    Please try below dax formula:

    Quantity =
VAR cur_field =
    SELECTEDVALUE ( 'Table'[CHANGED_FIELDS] )
VAR _a =
    FIND ( "quantity", cur_field,, BLANK () )
VAR _b =
    FIND ( "updated_at", cur_field,, BLANK () )
VAR _c =
    FIND ( "updated_by_id", cur_field,, BLANK () )
VAR _val =
    IF ( NOT ( ISBLANK ( _b ) ), _b, IF ( NOT ( ISBLANK ( _c ) ), _c ) )
RETURN
    IF ( ISBLANK ( _a ), BLANK (), MID ( cur_field, _a + 12, _val - _a - 16 ) )

    Updated at = 
var cur_field=SELECTEDVALUE('Table'[CHANGED_FIELDS])
var _a=FIND("updated_at",cur_field,,BLANK())
return
IF(ISBLANK(_a),BLANK(),MID(cur_field,_a+14,19))
    Updated_by_id =
VAR cur_field =
    SELECTEDVALUE ( 'Table'[CHANGED_FIELDS] )
VAR _lenth =
    LEN ( cur_field )
VAR _a =
    FIND ( "updated_by_id", cur_field,, BLANK () )
RETURN
    IF ( ISBLANK ( _a ), BLANK (), MID ( cur_field, _a + 17, _lenth - _a - 18 ) )

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi SzymonKl ,

Please try to create three measure with below dax formula:

Quantity =
VAR cur_field =
    SELECTEDVALUE ( 'Table'[CHANGED_FIELDS] )
VAR _a =
    FIND ( "quantity", cur_field )
VAR _b =
    FIND ( "updated_at", cur_field )
RETURN
    MID ( cur_field, _a + 11, _b - _a - 13 )

Updated at =
VAR cur_field =
    SELECTEDVALUE ( 'Table'[CHANGED_FIELDS] )
VAR _a =
    FIND ( "updated_at", cur_field )
RETURN
    MID ( cur_field, _a + 13, 19 )

Updated_by_id =
VAR cur_field =
    SELECTEDVALUE ( 'Table'[CHANGED_FIELDS] )
VAR _lenth =
    LEN ( cur_field )
VAR _a =
    FIND ( "updated_by_id", cur_field )
RETURN
    MID ( cur_field, _a + 16, _lenth - _a - 17 )

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • SzymonKl's avatar
    SzymonKl
    Helper I
    3 years ago

    Hi Anonymous 

    Thank you that you found time to help me. Unfortunately, this solution does not work in my case for some reason. Can the difference in the length of "changed_fields" be important here? If so, I am sorry that in the question, I did not give you a wider table example:

    CHANGED_FIELDS

    {"quantity": "2997770", "updated_at": "2023-03-02 10:43:37", "updated_by_id": "53"}
    {"quantity": "2997788", "updated_at": "2023-03-02 15:23:45", "updated_by_id": "55"}
    {"quantity": "2997800", "updated_at": "2023-03-02 15:24:00"}
    {"quantity": "2999282", "updated_at": "2023-03-02 00:01:22", "updated_by_id": "55"}
    {"quantity": "300", "updated_at": "2023-03-02 10:24:52"}
    {"quantity": "302", "updated_at": "2023-03-02 10:24:36"}
    {"quantity": "304", "updated_at": "2023-03-02 10:24:20"}
    {"quantity": "307", "updated_at": "2023-03-02 10:24:04"}

     

    Here's what I received:

    Quantity1 =
    var cur_field=SELECTEDVALUE('ASSET_LOGS'[CHANGED_FIELDS])
    var _a=FIND("quantity",cur_field)
    var _b=FIND("updated_at",cur_field)
    return
    MID(cur_field,_a+11,_b-_a-13)
    Error :

     

    Updated at =
    var cur_field=SELECTEDVALUE('ASSET_LOGS'[CHANGED_FIELDS])
    var _a=FIND("updated_at",cur_field)
    return
    MID(cur_field,_a+13,19)
     
    Error:

     

    Updated_by_id1 =
    var cur_field=SELECTEDVALUE('ASSET_LOGS'[CHANGED_FIELDS])
    var _lenth=LEN(cur_field)
    var _a=FIND("updated_by_id",cur_field)
    return
    MID(cur_field,_a+16,_lenth-_a-17)

    Please, if possible, can you help me?

    Thank You

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi SzymonKl ,

      Please try below dax formula:

      Quantity =
VAR cur_field =
    SELECTEDVALUE ( 'Table'[CHANGED_FIELDS] )
VAR _a =
    FIND ( "quantity", cur_field,, BLANK () )
VAR _b =
    FIND ( "updated_at", cur_field,, BLANK () )
VAR _c =
    FIND ( "updated_by_id", cur_field,, BLANK () )
VAR _val =
    IF ( NOT ( ISBLANK ( _b ) ), _b, IF ( NOT ( ISBLANK ( _c ) ), _c ) )
RETURN
    IF ( ISBLANK ( _a ), BLANK (), MID ( cur_field, _a + 12, _val - _a - 16 ) )

      Updated at = 
var cur_field=SELECTEDVALUE('Table'[CHANGED_FIELDS])
var _a=FIND("updated_at",cur_field,,BLANK())
return
IF(ISBLANK(_a),BLANK(),MID(cur_field,_a+14,19))
      Updated_by_id =
VAR cur_field =
    SELECTEDVALUE ( 'Table'[CHANGED_FIELDS] )
VAR _lenth =
    LEN ( cur_field )
VAR _a =
    FIND ( "updated_by_id", cur_field,, BLANK () )
RETURN
    IF ( ISBLANK ( _a ), BLANK (), MID ( cur_field, _a + 17, _lenth - _a - 18 ) )

      Please refer the attached .pbix file.

       

      Best regards,
      Community Support Team_Binbin Yu
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • SzymonKl's avatar
        SzymonKl
        Helper I
        3 years ago

        Anonymous Thanks You 

         

        Calculations "Update_at and  Updated _by_id works perfectly , unfortunetlly i getting this error with "Quantity" :