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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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