Forum Discussion

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

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.

Split column_V2.pbix24 KB

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.

split column.pbix37 KB

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
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.
Split column_V2.pbix24 KB
- 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" :
  - Anonymous
    Not applicable
    3 years ago
    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.