how to summarize table and pick values for new columns

[Uki]

I know that it's basics, but having no background I could not have found a solution for my problem even though I have researched for a while.

Lets say I have a table:

link_id	pipeline	owner_initials
1	1	AA
1	2	BB
2	1	CC
2	2	DD
3	1	AA
3	2	DD

and what I want to get is a table like:

link_id	SDR	KAM
1	AA	BB
2	CC	DD
3	AA	DD

(SDR is owner_initials for pipeline 1, KAM is owner_initials for pipeline 2).

What is the most elegant way to do it either in M or DAX?

Thank you!
Łukasz

[Uki]

This is what i did by myself, but I did not come up with this part:

'Table'[link_id] = EARLIER( [link_id] )

 
Reading abou EARLIER now. Not that obvious at my level 😞

[tamerj1 · Accepted solution]

Hi @Uki 
Please refer to sample file https://www.dropbox.com/t/tkYPptvUVdvfRvr1

SDR = 
CALCULATE (
        SELECTEDVALUE ( 'Pipe line'[owner_initials] ),
        'Pipe line'[pipeline] = 1
)

KAM = 
CALCULATE (
        SELECTEDVALUE ( 'Pipe line'[owner_initials] ),
        'Pipe line'[pipeline] = 2
)

[Uki]

Thank you! Works!

[VahidDM]

Hi @Uki 

Try this code to add a new table with DAX:

New Table = 
ADDCOLUMNS(
    VALUES( 'Table'[link_id] ),
    "SDR",
        CALCULATE(
            MAX( 'Table'[owner_initials] ),
            FILTER(
                'Table',
                'Table'[link_id] = EARLIER( [link_id] )
                    && 'Table'[pipeline] = 1
            )
        ),
    "KAM",
        CALCULATE(
            MAX( 'Table'[owner_initials] ),
            FILTER(
                'Table',
                'Table'[link_id] = EARLIER( [link_id] )
                    && 'Table'[pipeline] = 2
            )
        )
)

Output:

Sample file attached.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/