Forum Discussion

AsaThor's avatar
AsaThor
New Member
1 year ago
Solved

Concatenate values based on new and old value

Hi there, 
I could need you help. 

I got a table that is looking like this:


IssueIdAUTHORCREATEDFIELDOLDVALUENEWVALUEpkey
3370181[email protected]2023-02-09 14:30:47.000Fix VersionNULLVERSION-17.0.51AMAIN-23443
3370181[email protected]2023-02-10 10:23:07.000Fix VersionNULLVERSION-17.0.52MAIN-23443
3370181[email protected]2023-02-10 10:23:07.000Fix VersionVERSION-17.0.51ANULLMAIN-23443
3370181[email protected]2023-02-10 10:23:07.000Fix VersionNULLVERSION-17.2.29MAIN-23443
3370181[email protected]2023-02-10 10:23:07.000TimeEstimate300400MAIN-23443
3370181[email protected]2023-02-10 10:25:31.000Fix VersionVERSION-17.0.52NULLMAIN-23443
3370181[email protected]2023-02-10 10:25:31.000Fix VersionNULLVERSION-17.0.53MAIN-23443

 

I want to add a new column thats checking if a Version is assigned to a NEWVALUE. Then it should use this version. N Version can be assiged to new values without beeing removed. Removed means Field =Fixed Version and newvalue for this Version is NULL.

To be more clear plz take a look at my last column here. It should look like this in the end and it should be grouped by pkey as the pkey will change. 

IssueIdAUTHORCREATEDFIELDOLDVALUENEWVALUEpkeyCorrect
3370181[email protected]2023-02-09 14:30:47.000Fix VersionNULLVERSION-17.0.51AMAIN-23443VERSION-17.0.51A
3370181[email protected]2023-02-10 10:23:07.000Fix VersionNULLVERSION-17.0.52MAIN-23443VERSION-17.0.51A,VERSION-17.0.52
3370181[email protected]2023-02-10 10:23:07.000Fix VersionVERSION-17.0.51ANULLMAIN-23443VERSION-17.0.52
3370181[email protected]2023-02-10 10:23:07.000Fix VersionNULLVERSION-17.2.29MAIN-23443VERSION-17.0.52,VERSION-17.2.29
3370181[email protected]2023-02-10 10:23:07.000TimeEstimate300400MAIN-23443VERSION-17.0.52,VERSION-17.2.29
3370181[email protected]2023-02-10 10:25:31.000Fix VersionVERSION-17.0.52NULLMAIN-23443VERSION-17.2.29
3370181[email protected]2023-02-10 10:25:31.000Fix VersionNULLVERSION-17.0.53MAIN-23443VERSION-17.2.29,VERSION-17.0.53


I am thinking of something like an indexed column grouped by pkey that is populated with the values from NewValue and Oldvalue if the Field = Fix Version. Do you know how to solve this? Thx in advance.

Thorsten


 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi AsaThor  - i have created a calculated column as below:

     

    Please check

     

     

    you can replace table name as per your model. 

     

    Correct =
    VAR CurrentPKey = 'concc'[pkey]
    VAR CurrentCreated = 'concc'[CREATED]
    VAR AllRows =
        FILTER(
            'concc',
            'concc'[pkey] = CurrentPKey &&
            'concc'[CREATED] <= CurrentCreated
        )
    VAR Additions =
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(AllRows, 'concc'[FIELD] = "Fix Version" && NOT(ISBLANK('concc'[NEWVALUE]))),
                "Version", 'concc'[NEWVALUE]
            )
        )
    VAR Removals =
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(AllRows, 'concc'[FIELD] = "Fix Version" && NOT(ISBLANK('concc'[OLDVALUE]))),
                "Version", 'concc'[OLDVALUE]
            )
        )
    VAR ActiveVersions =
        EXCEPT(Additions, Removals)
    RETURN
        CONCATENATEX(ActiveVersions, [Version], ", ")

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi AsaThor  - i have created a calculated column as below:

     

    Please check

     

     

    you can replace table name as per your model. 

     

    Correct =
    VAR CurrentPKey = 'concc'[pkey]
    VAR CurrentCreated = 'concc'[CREATED]
    VAR AllRows =
        FILTER(
            'concc',
            'concc'[pkey] = CurrentPKey &&
            'concc'[CREATED] <= CurrentCreated
        )
    VAR Additions =
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(AllRows, 'concc'[FIELD] = "Fix Version" && NOT(ISBLANK('concc'[NEWVALUE]))),
                "Version", 'concc'[NEWVALUE]
            )
        )
    VAR Removals =
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(AllRows, 'concc'[FIELD] = "Fix Version" && NOT(ISBLANK('concc'[OLDVALUE]))),
                "Version", 'concc'[OLDVALUE]
            )
        )
    VAR ActiveVersions =
        EXCEPT(Additions, Removals)
    RETURN
        CONCATENATEX(ActiveVersions, [Version], ", ")
  • AsaThor's avatar
    AsaThor
    New Member
    1 year ago

    I will have a look but what I see on your output seems not to get the correct values:

     

    • rajendraongole1's avatar
      rajendraongole1
      Icon for Super User rankSuper User
      1 year ago

      Hi AsaThor  - i have create a explicit measure. 

       

      Correct2 =
      VAR CurrentPKey = 'concc'[pkey]
      VAR CurrentCreated = 'concc'[CREATED]
      VAR AllRows =
          FILTER(
              'concc',
              'concc'[pkey] = CurrentPKey && 'concc'[CREATED] <= CurrentCreated
          )
      VAR Additions =
          DISTINCT(
              SELECTCOLUMNS(
                  FILTER(AllRows, 'concc'[FIELD] = "Fix Version" && NOT(ISBLANK('concc'[NEWVALUE]))),
                  "Version", 'concc'[NEWVALUE]
              )
          )
      VAR Removals =
          DISTINCT(
              SELECTCOLUMNS(
                  FILTER(AllRows, 'concc'[FIELD] = "Fix Version" && NOT(ISBLANK('concc'[OLDVALUE]))),
                  "Version", 'concc'[OLDVALUE]
              )
          )
      VAR ActiveVersions =
          EXCEPT(Additions, Removals)
      RETURN
          CONCATENATEX(ActiveVersions, [Version], ", ")
       

       

      Please check the attached code.