summarize table and add new columns

[Anonymous]

I have a tall table: ID Status date 1 1 01-Jan 1 2 02-Jan 2 1 03-Jan 2 2 08-Jan 2 3 11-Jan 2 2 16-Jan 3 1 21-Jan 3 2 26-Jan 3 4 28-Jan 3 5 31-Jan I would like to make a short table, like this (each Status column shows the maximum date where the ID and status as as indicated): ID Status 1 Status 2 Status 3 Status 4 Status 5 current 1 01-Jan 02-Jan 2 2 03-Jan 16-Jan 11-Jan 2 3 21-Jan 26-Jan 28-Jan 31-Jan 5 I tried various combinations in DAX using SUMMARIZE(),SELECTCOLUMNS(),MAX(),: Status Changes = max(SELECTCOLUMNS(FILTER(Table1,Table1[ID]=1),"Status 1",Table1[date])) as well as searching for similar messages: https://community.powerbi.com/t5/Desktop/Summarize-table-and-add-new-column/m-p/557901/highlight/tru... https://community.powerbi.com/t5/Desktop/Add-a-new-column-to-a-summarized-table/m-p/202929/highlight... To no avail. Any suggestions are appreciated.

[v-lili6-msft]

hi, @Anonymous

Your expected output is a bit messy, and if I understand you correctly, you could try this way as below:

First add a current status column

In Edit Queries, Duplicate the basic table use Group by and merge function like this post.

https://community.powerbi.com/t5/Desktop/Findin-MAX-from-multiple-columns-in-query-editor/td-p/36329...

Second use Pivot column function like below:

Select Status column and then click Transform->Pivot column

Result:

and here is pbix file, please try it.

By the way, you could change another browser eg. IE, It will be well.

Best Regards,

Lin

[Anonymous]

Love it!! Can this be done in DAX?

[v-lili6-msft · Accepted solution]

hi, @Anonymous

I'm afraid it is difficult achieve in DAX, this has changed table structure. and unless you use use a matrix visual to show the result and do not show current value in visual.

here is the pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Love it!! Can this be done in DAX?

[Anonymous]

Apologies for the messy post, the webpage is not letting me input in html, nor is it recognizing new line characters