cancel
Showing results for
Did you mean:
Regular Visitor

## Another rankx question

Alright, @mods/devs first off this forum lets you writeup a post without signing in then deletes that post when you go to sign in after the prompt....kinda a bummer.

I'm playing around with FiveThirtyEight's Bob Ross data set. What I would like to do is view top N themes by year or quarter so we could chart if there was any variation. However, my attempts to do so have rankx returning just the count, not the rank. E.G. if a theme occurred five times, the rank is five, if once the rank is 1, if 10 the rank is 10. The rank returned is the same whether rankx is set to rank descending or ascending.

Sample input data:

 Date TITLE drawing_theme event 1983-01-11 A WALK IN THE WOODS BUSHES 1 1983-01-11 A WALK IN THE WOODS DECIDUOUS 1 1983-01-11 A WALK IN THE WOODS GRASS 1 1983-01-11 A WALK IN THE WOODS RIVER 1 1983-01-11 A WALK IN THE WOODS TREE 1 1983-01-11 A WALK IN THE WOODS TREES 1 1983-01-11 MT. MCKINLEY CABIN 1 1983-01-11 MT. MCKINLEY CLOUDS 1 1983-01-11 MT. MCKINLEY CONIFER 1

Function:

``````/// the first rank is calculated as:
Rank = RANKX(
ALL(bob_stack[drawing_theme]),
CALCULATE(
SUM(bob_stack[event])
),, DESC
)
/// the second rank is calculated as so:
Rank2 = RANKX(
ALL(bob_stack[drawing_theme]),
CALCULATE(
SUM(bob_stack[event])
)
)``````

Results:

 drawing_theme sum(event) Rank Rank2 APPLE_FRAME 1 1 1 AURORA_BOREALIS 2 2 2 BARN 17 17 17 BEACH 27 27 27 BOAT 2 2 2 BRIDGE 7 7 7 BUILDING 1 1 1 BUSHES 120 120 120

It's also worth noting that the calculate doesn't do anything in this case either, I'm merely using it because this bloke had a working rankx and seemed to think it was important.

I'm not sure how dynamic this calculation is so I will also mention that removal of the sum(event) column also has no effect.

1 ACCEPTED SOLUTION
Super User II

I think you are running into trouble because you have to remove the context of episode or it gets in the way.

``````Ranking =
CALCULATE (
RANKX ( ALL ( bob_stack[drawing_theme] ), [Occurrances],, DESC, DENSE ),
REMOVEFILTERS ( bob_stack[EPISODE] )
)``````

I have attached my sample file for you to look at.

Super User II

I think you are running into trouble because you have to remove the context of episode or it gets in the way.

``````Ranking =
CALCULATE (
RANKX ( ALL ( bob_stack[drawing_theme] ), [Occurrances],, DESC, DENSE ),
REMOVEFILTERS ( bob_stack[EPISODE] )
)``````

I have attached my sample file for you to look at.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors