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

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.

5 Replies

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