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: IssueId AUTHOR CREATED FIELD OLDVALUE NEWVALUE pkey 3370181 [email protected] 2023-02-09 14:30:47...
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], ", ")
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.