locked
DAX Custom column to make first row + second row until (i-IV) found. RRS feed

  • Question

  • Hi Guys,

    I have below data

    2001  
    I 56.9
    II 63.4
    III 61.5
    IV 64.1
    2002  
    I 64.8
    Annual 63.7

    I want to create custom column which has results as follow based on 1st row.

    2001   2001-Final
    I 56.9 2001-I
    II 63.4 2001-II
    III 61.5 2001-III
    IV 64.1 2001-IV
    2002   2002-Final
    I 64.8 20012-I
    Annual 63.73 20012-Annual

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    Saturday, December 24, 2016 5:40 AM

Answers

  • Hi Navind,

    In the source table, is the data’s format like the following screenshot? If it is, we are not able to do in Power Pivot. Because the calculation is based on column, there is no certain regular in different rows. Maybe you can post it to Power Query forum to get professional support for translation the data when import the data. 



    If the data for 2001 and 2002 years are in different table, please refer to the following solution.

    The first picture shows the sample data table.



    Create calculated  columns to get the first row year in each row, then calculate what you want. Please see the desired result shown in the following screenshot.

    =IF(ISBLANK(Table3[Column2]),Table3[Column1],BLANK())
    =SUM([get year])
    =IF(ISBLANK(Table3[Column2]),BLANK(),CONCATENATE(CONCATENATE(Table3[get year in each row],"-"),Table3[Column1]))  



    If you have any other issue, please feel free to ask.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Michael Amadi Saturday, December 31, 2016 5:38 PM
    • Marked as answer by AV111Editor Thursday, January 5, 2017 11:41 AM
    Monday, December 26, 2016 6:08 AM

All replies

  • Are you sure about your desired result?

    Doesn't look consistent in my eyes. How about this:

    2001   2001-Final
    I 56.9 2001-I
    II 63.4 2001-II
    III 61.5 2001-III
    IV 64.1 2001-IV
    2002   2002-Final
    I 64.8 2002-I
    Annual 63.73 2002-Annual


    Imke Feldmann TheBIccountant.com

    Saturday, December 24, 2016 2:39 PM
    Answerer
  • Hi Navind,

    In the source table, is the data’s format like the following screenshot? If it is, we are not able to do in Power Pivot. Because the calculation is based on column, there is no certain regular in different rows. Maybe you can post it to Power Query forum to get professional support for translation the data when import the data. 



    If the data for 2001 and 2002 years are in different table, please refer to the following solution.

    The first picture shows the sample data table.



    Create calculated  columns to get the first row year in each row, then calculate what you want. Please see the desired result shown in the following screenshot.

    =IF(ISBLANK(Table3[Column2]),Table3[Column1],BLANK())
    =SUM([get year])
    =IF(ISBLANK(Table3[Column2]),BLANK(),CONCATENATE(CONCATENATE(Table3[get year in each row],"-"),Table3[Column1]))  



    If you have any other issue, please feel free to ask.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Michael Amadi Saturday, December 31, 2016 5:38 PM
    • Marked as answer by AV111Editor Thursday, January 5, 2017 11:41 AM
    Monday, December 26, 2016 6:08 AM