Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

summarize table and add new columns

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/true#M262953 https://community.powerbi.com/t5/Desktop/Add-a-new-column-to-a-summarized-table/m-p/202929/highlight/true#M89315 To no avail. Any suggestions are appreciated.
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Apologies for the messy post, the webpage is not letting me input in html, nor is it recognizing new line characters
  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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/363299

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Love it!! Can this be done in DAX?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Love it!! Can this be done in DAX?

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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