Answered by:
Dynamic Row Numbering
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 TSQL. (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/tsqltodaxsimulatingrankfunction.html
http://visakhm.blogspot.ae/2012/09/denseranksimulationindax.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 PageThursday, 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 PageThursday, 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