Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JJohns
Frequent Visitor

Ranking in Ascending order not filtering properly

Hello, 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 have to hardcode everything as well within measures as I am combining several metrics into one matrix that cannot all be filtered the same. I have tried several variations of the following code:

IF (
ISFILTERED ( 'Roster'[Office #2] ),
RANKX ( ALL ( 'Roster'[Office #2] ), _Ranking_Measures[Data_%]),
IF (
ISFILTERED ( 'Roster'[State] ),
RANKX ( ALL ( 'Roster'[State] ), _Ranking_Measures[Data_%] )
)
)
 
 
Attached are results at State Level
JJohns_0-1657912968050.png

 

Results when drill down to IL:

JJohns_1-1657913036855.png

I am just trying to figure out how to return 1-6 when drilled down. RANKX is properly ranking within the subset, just not using the filtered down count of offices for the ranking numbers. Any help is appreciated. Thanks



3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @JJohns ,

 

According to your description, if you want to dynamically rank the results based on filtering, you can use the allselect function.

Keep the results of filtering in the external filtering context. The reference is as follows.

Rank_ =
IF (
    HASONEVALUE ( DimProduct[Color] ),
    RANKX ( ALLSELECTED ( DimProduct[Color] ), [Amount_] ),
    BLANK ()
)

vhenrykmstf_0-1658307560917.png

vhenrykmstf_1-1658307583558.png


If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey Henry I accidently accepted this as a solution but I am still having the same issue. 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 Rank 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:
JJohns_0-1658346799911.png

Results when expanded to office:

JJohns_1-1658346862651.png

Results I am expecting:

JJohns_2-1658346917029.png

Table used:

StateOfficeAmount
SC10039
SC10129
SC10242
SC10345
SC10476
SC10595
SC10692
SC10787
IL20077
IL2013
IL20250
IL20367
IL20425
IL20571
IL20658
IL20772
IL20888
IL20978
IN30037
IN30180
IN30239
IN3034
IN30482
IN30560
IN30659

I tried altering the code to mimic yours and it still doesn't rank correctly once the row level is expanded. Any help is greatly appreciated. Thanks




lbendlin
Super User
Super User

" I have to hardcode everything as well within measures"  not sure what you mean by that. seems mutually exclusive.

 

Note that if you use RANKX in measures you need to do a couple extra steps, and you need to stay aware of the filter context for each.

 

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors