bhtan
August 25, 2023, 6:51am
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.
Here the MDX:
Rank(
[Sprint].CurrentHierarchyMember,
Order(
[Sprint].[Sprint].Members,
[Measures].[Sprint Story Points committed],
DESC
)
)
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
bhtan
September 1, 2023, 6:21am
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
)
)
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
bhtan
September 1, 2023, 9:09am
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.