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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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