undefined

[SzymonKl]

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 only solution I see here is to Create 3 New Measures. The first measure, named: "Quantity", include a number in the example above "0". The second measure, named "updated_at" include the date/time in the example above "2023-03-01 09:44:58, The third measure, named "updated_by_id" include the number in the example above "453". The column  "CHANGED_FIELDS" is located in "Asset_Logs" table.

Thank you

[Anonymous]

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]

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 · Accepted solution]

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]

@Anonymous Thanks You 

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

[Anonymous]

Hi @SzymonKl ,

Please confirm your mesure is correct, the dax formula works well in my data, if it still not work, please give more informations.

Thanks for your efforts & time in advance.

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.