How to get Rank to start from 1

Questions & Answers
1

Hi I am trying to get the rank to start from 1 but it is not showing. Here an example I am ranking sprints based on Story points committed. I have filtered to select a few sprints. I would like to do the same but rank according to Sprint Start Date as well.

image

Here the MDX:

Rank(
  [Sprint].CurrentHierarchyMember,  
  Order(
    [Sprint].[Sprint].Members,
    [Measures].[Sprint Story Points committed],
    DESC
  )
)
2

Hi @bhtan

Thanks for sharing your use case!
I recommend trying either this formula below:

Rank(
   [Sprint].CurrentMember,
 Order(
   Filter(
     VisibleRowsSet(),
     [Measures].[Sprint Story Points committed] > 0
   ),
  [Measures].[Sprint Story Points committed],
  BDESC
 )
)

Or this one:

Rank(
   [Sprint].CurrentMember,
   Order(
    Filter(
    [Sprint].[Sprint].Members,
    [Measures].[Sprint Story Points committed] > 0
    ),
   [Measures].[Sprint Story Points committed],
   BDESC
   )
)

Best wishes,

Elita from support@eazybi.com

3

Hi Elita,

Thanks for the reply. This code works but when I tried to order the column or filter to match number 1, the rank shows all 0.

Rank(
   [Sprint].CurrentMember,
 Order(
   Filter(
     VisibleRowsSet(),
     [Measures].[Sprint Story Points committed] > 0
   ),
  [Measures].[Sprint Story Points committed],
  BDESC
 )
)
4

Hi @bhtan

If you’d like to filter the column, please try using the other code and see if it works for you:

Rank(
   [Sprint].CurrentMember,
   Order(
    Filter(
    [Sprint].[Sprint].Members,
    [Measures].[Sprint Story Points committed] > 0
    ),
   [Measures].[Sprint Story Points committed],
   BDESC
   )
)

Best wishes,

Elita from support@eazybi.com

5

Hi Elita,

Yes I have tried the second measure, ordering works but it doesn’t start from 1 in the table.

I am trying to create a gauge with value, so I want to rank and filter the top 1.

6

Hi @bhtan
My sincere apologies for the delayed response.

When I use the last measure I shared with you, I am able to add a filter to the measure by clicking on the measure name → Filter Rows-> “= 1”

image
image1640×1176 80.8 KB

And then the Rows are filtered according to the filter:

image
image1298×460 32.3 KB

Best wishes,

Elita from support@eazybi.com