topic Re: how to summarize table and pick values for new columns in DAX Commands and Tips

how to summarize table and pick values for new columns

Uki — Wed, 23 Mar 2022 19:27:49 GMT

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

Re: how to summarize table and pick values for new columns

VahidDM — Wed, 23 Mar 2022 23:57:33 GMT

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/

Re: how to summarize table and pick values for new columns

tamerj1 — Thu, 24 Mar 2022 06:46:36 GMT

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 )

Re: how to summarize table and pick values for new columns

Uki — Thu, 24 Mar 2022 08:29:03 GMT

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 😞

Re: how to summarize table and pick values for new columns

Uki — Thu, 24 Mar 2022 08:29:47 GMT

Thank you! Works!