locked
How to calculate RowNumber of a row in DAX RRS feed

  • Question

  • Hello,

    I have a requirement to find the rowmumbers based on a group. I know there is Rownumber function in sql. How can I implement the same in DAX. PFA screenshot. Rownumber is the calculated field i want based on the id and name column group.

    Monday, April 20, 2015 10:17 AM

Answers

  • Hi Shamsuddeen,

    I've unit tested the following so it should work for you; -

    RowCount
    =RANKX(YourTableName,[Name],,1,Dense)
    

    Please see the following link on RANKX.

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Tuesday, April 21, 2015 8:04 PM
  • OK,

    I hoped what I did with RANKX was close enough. Have you tried experimenting with the VALUES function to meet this requirement?


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Wednesday, April 22, 2015 8:30 PM
  • Thanks Kieran for the reply.

    However it does not work as the same already tried with filters as well as I need to get rownumber based on the group. Also as you noticed since it tries to find the rank if it has the same value it gives the smae rank, but our requirement is to get rownumber,

    =RANKX(FILTER(Sheet1,EARLIER([Name])=[Name]&&EARLIER([Id])=[Id]),,[Salary],1,dense) 

    Hi Shamsudden,

    According to your description, you need to the rownumber based on ID and Name column, right?

    I have tested it on my local environemnt. As you said, rankx function gives the same rank if it has the same value.

    Based on my research, it's default setting that rankx function gives the same rank if it has the same value. And there is no other way to return rownumber is DAX function.

    If you have any concern about this behavior, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, April 23, 2015 3:30 AM
  • Your table looks like as if there has been a unique identifier before (Date, month i.e.) – why has this been lost? Can you get it back?

    If that’s not possible and it still makes sense to operate on ID-row-level (instead of grouping at this stage), you can use Power Pivots ETL-sister: Power Query. So instead of taking your table directly into Power Pivot, you drag it through PowerQuery and load it to the model.

    The code would look like this: (Tabelle1 being the name of your table, this string could be adjusted to directly connect to your source)

    let RowNumber_ = (ID) =>

    let

        Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

        AddIndex = Table.AddIndexColumn(Quelle, "Index", 0, 1),

        FilterID = Table.SelectRows(AddIndex, each ([ID] = ID)),

        AddRowNumberOnIdLevel = Table.AddIndexColumn(FilterID, "RowNumber", 1, 1)

    in

        AddRowNumberOnIdLevel,

        Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

        AddIndex = Table.AddIndexColumn(Quelle, "Index", 0, 1),

        GroupRows = Table.Group(AddIndex, {"Name"}, {{"ID", each List.Min([ID]), type number}}),

        InvFunction = Table.AddColumn(GroupRows, "Custom", each RowNumber_([ID])),

        ShowFields = Table.ExpandTableColumn(InvFunction, "Custom", {"Salary", "RowNumber"}, {"Salary", "RowNumber"})

    in

        ShowFields


    Imke

    Thursday, April 23, 2015 8:09 PM
    Answerer

All replies

  • Hi Shamsuddeen,

    I've unit tested the following so it should work for you; -

    RowCount
    =RANKX(YourTableName,[Name],,1,Dense)
    

    Please see the following link on RANKX.

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Tuesday, April 21, 2015 8:04 PM
  • Thanks Kieran for the reply.

    However it does not work as the same already tried with filters as well as I need to get rownumber based on the group. Also as you noticed since it tries to find the rank if it has the same value it gives the smae rank, but our requirement is to get rownumber,

    =RANKX(FILTER(Sheet1,EARLIER([Name])=[Name]&&EARLIER([Id])=[Id]),,[Salary],1,dense) 

    Wednesday, April 22, 2015 7:45 AM
  • OK,

    I hoped what I did with RANKX was close enough. Have you tried experimenting with the VALUES function to meet this requirement?


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Wednesday, April 22, 2015 8:30 PM
  • Thanks Kieran for the reply.

    However it does not work as the same already tried with filters as well as I need to get rownumber based on the group. Also as you noticed since it tries to find the rank if it has the same value it gives the smae rank, but our requirement is to get rownumber,

    =RANKX(FILTER(Sheet1,EARLIER([Name])=[Name]&&EARLIER([Id])=[Id]),,[Salary],1,dense) 

    Hi Shamsudden,

    According to your description, you need to the rownumber based on ID and Name column, right?

    I have tested it on my local environemnt. As you said, rankx function gives the same rank if it has the same value.

    Based on my research, it's default setting that rankx function gives the same rank if it has the same value. And there is no other way to return rownumber is DAX function.

    If you have any concern about this behavior, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, April 23, 2015 3:30 AM
  • Your table looks like as if there has been a unique identifier before (Date, month i.e.) – why has this been lost? Can you get it back?

    If that’s not possible and it still makes sense to operate on ID-row-level (instead of grouping at this stage), you can use Power Pivots ETL-sister: Power Query. So instead of taking your table directly into Power Pivot, you drag it through PowerQuery and load it to the model.

    The code would look like this: (Tabelle1 being the name of your table, this string could be adjusted to directly connect to your source)

    let RowNumber_ = (ID) =>

    let

        Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

        AddIndex = Table.AddIndexColumn(Quelle, "Index", 0, 1),

        FilterID = Table.SelectRows(AddIndex, each ([ID] = ID)),

        AddRowNumberOnIdLevel = Table.AddIndexColumn(FilterID, "RowNumber", 1, 1)

    in

        AddRowNumberOnIdLevel,

        Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

        AddIndex = Table.AddIndexColumn(Quelle, "Index", 0, 1),

        GroupRows = Table.Group(AddIndex, {"Name"}, {{"ID", each List.Min([ID]), type number}}),

        InvFunction = Table.AddColumn(GroupRows, "Custom", each RowNumber_([ID])),

        ShowFields = Table.ExpandTableColumn(InvFunction, "Custom", {"Salary", "RowNumber"}, {"Salary", "RowNumber"})

    in

        ShowFields


    Imke

    Thursday, April 23, 2015 8:09 PM
    Answerer