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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JJohns
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:
JJohns_0-1658926581129.png

 

Results when expanded to office:

JJohns_1-1658926581241.png

 

Results I am expecting:

JJohns_2-1658926581263.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

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

@JJohns As per your data,

 

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]
    )
    ))

 

Mohan1029_0-1658928776342.png

Accept the solution if it helps you.

Thanks,

Mohan V

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@JJohns As per your data,

 

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]
    )
    ))

 

Mohan1029_0-1658928776342.png

Accept the solution if it helps you.

Thanks,

Mohan V

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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