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 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.