cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Ascending order in Rankx not filtering

Apologies if this post seems familar, I accidently accepted a solution in my previous post but I am am still having the same issue. I have included a sample dataset that mimics the issue I am having. I have a data set that I am ranking each hierarchy in ascending order. The first level works correctly and ranks properly, the issue is that once I drill down to the next level rankx correctly ranks the subset but wants to start at the bottom of the entire population vs ranking the subset. I am attaching a sample test set that replicates my exact issue. The table has 3 columns State, Office, Amount. I created a measure: Amount_2 = CALCULATE(SUM(Amount)) so that I have a measure to rank. I then use the following Dax for my Rankx measure:

Rank =
IF(
ISFILTERED ( Data[Office] ),
RANKX ( ALLSELECTED(Data[Office]), Data[Amount_2],,ASC) ,

IF (
ISFILTERED ( Data[State] ),
RANKX ( ALLSELECTED(Data[State]), Data[Amount_2],,ASC)
)
)

After creating the measure I create a matrix with state and office as row variables, amount and rank in the values. I get the following results:

Results when expanded to office:

Results I am expecting:

Table used:

 State Office Amount SC 100 39 SC 101 29 SC 102 42 SC 103 45 SC 104 76 SC 105 95 SC 106 92 SC 107 87 IL 200 77 IL 201 3 IL 202 50 IL 203 67 IL 204 25 IL 205 71 IL 206 58 IL 207 72 IL 208 88 IL 209 78 IN 300 37 IN 301 80 IN 302 39 IN 303 4 IN 304 82 IN 305 60 IN 306 59

Any help is greatly appreciated. Thanks
1 ACCEPTED SOLUTION
Anonymous
Not applicable

You can use the below dax to get the ranks based on states and Office's

``````Rank_Office Level =
IF(ISINSCOPE(Ranking[Office]),
RANKX(
CALCULATETABLE(VALUES(Ranking[Office]),
ALLSELECTED(Ranking[Office])
),
[Total Amount]
),
IF(ISINSCOPE(Ranking[State]),
RANKX(
ALLSELECTED(Ranking[State]),
[Total Amount]
)
))``````

Accept the solution if it helps you.

Thanks,

Mohan V

2 REPLIES 2
Anonymous
Not applicable

You can use the below dax to get the ranks based on states and Office's

``````Rank_Office Level =
IF(ISINSCOPE(Ranking[Office]),
RANKX(
CALCULATETABLE(VALUES(Ranking[Office]),
ALLSELECTED(Ranking[Office])
),
[Total Amount]
),
IF(ISINSCOPE(Ranking[State]),
RANKX(
ALLSELECTED(Ranking[State]),
[Total Amount]
)
))``````

Accept the solution if it helps you.

Thanks,

Mohan V

Frequent Visitor

Thanks Mohan, that worked perfectly, I appreciate the help!