locked
Dynamic Row Numbering RRS feed

  • Question

  • Hi everyone

    This seems simple in theory but I am having trouble making this DAX formula work. 

    This is my test data including the column "rank" which is a row_number with partition by generated using T-SQL. (The nature of the data is that the "Measure" will always increase cumulatively per month and I want the maximum "measure" to equal a rank of 1).

    What I need is a second RANK column which updates dynamically so that if I select just some of the Months, they will rank from 1 +. E.g:

    Thanks for your help

    Andrew

    Thursday, November 12, 2015 1:26 PM

Answers

  • This will not work in the Power Pivot model.

    This must be implemented as a measure, not a calculated column in the model, and will only work in a pivot table where you've brought that measure into the Values area of the pivot table.

    GNet Group BI Consultant

    • Marked as answer by Quietandy Thursday, November 12, 2015 4:03 PM
    Thursday, November 12, 2015 3:24 PM

All replies

  • you mean this?

    http://visakhm.blogspot.ae/2013/07/t-sql-to-dax-simulating-rank-function.html

    http://visakhm.blogspot.ae/2012/09/dense-rank-simulation-in-dax.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 12, 2015 1:43 PM
  • Quietandy,

    You can't get the functionality you want in a calculated column in the Power Pivot model. Calculated columns are only evaluated at model refresh time. If you want to dynamically rank the range you've selected, you'll need to create a measure similar to the ranking in the links Visakh provided, but in a CALCULATE(<rank>, ALLSELECTED(<filters you'd like to respect>))

    GNet Group BI Consultant

    Thursday, November 12, 2015 2:20 PM
  • Thanks both for the assistance. OK I have a measure currently:

    Count of People:=CALCULATE(DISTINCTCOUNT([DIM_Person_ID]),FILTER(mytable,[rank]=MIN([rank])))

    This worked except for in the example of the 2nd image above where "2" is the minimum rank value but the DAX is looking for a "1" and thus excluding the person from the query result for this particular month selection. How do I change this to pick up just the minimum value in the current context?

    Thanks again



    • Edited by Quietandy Thursday, November 12, 2015 3:14 PM Clarity
    Thursday, November 12, 2015 2:53 PM
  • This will not work in the Power Pivot model.

    This must be implemented as a measure, not a calculated column in the model, and will only work in a pivot table where you've brought that measure into the Values area of the pivot table.

    GNet Group BI Consultant

    • Marked as answer by Quietandy Thursday, November 12, 2015 4:03 PM
    Thursday, November 12, 2015 3:24 PM
  • The illustration shown in my post is created as a calculated column not as a measure

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 12, 2015 3:39 PM
  • Visakh, I am not saying that you cannot implement a ranking function as a calculated column in the model. I am saying that based on the requirements in this thread, that the rank be sensitive to filter selections, the solution must be implemented as a measure. Calculated columns are not dynamic. They change only on model refresh, not on filter context.

    GNet Group BI Consultant

    Thursday, November 12, 2015 4:15 PM