Skip to main content
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

Frequent Visitor

"Ranking with a Twist" Rankx based on a Measure


I have spent a good deal of time browsing for a solution, and have decided to create my first post after not finding a similar (enough) situation.



We are rolling out a new process/software at various locations and want to capture adaptation rates and proficiencies for each location as the "go live".

The model is extremely simple: one Fact Table, one Calendar Table and one Dimension Table.

I'm trying to generate a rank for each location based on a measure value. Since more/new/different locations will be added and removed over time only the locations (Divisions) that have data in the latest month should be considered for this ranking.
I have done that by using the following measures:

This is the base Metric:


Aging - Complete to Released to Bill =
AVERAGE ( Compliance[Complete to Released to Bill] )



This returns the above for the latest month with data:


Complete to Release (This Month) =
    [Aging - Complete to Released to Bill],
    FILTER ( 'Calendar', 'Calendar'[MonthYear] = [Max Month] )



This returns the latest month with transaction info: [Load count] is just a basic distinctcount()


Max Month =
    MAX ( 'Calendar'[MonthYear] ),
    FILTER ( ALL ( 'Calendar' ), [Load Count] )



Finally - the Measure to rank Divisions that have data:


CTR = 
    NOT(ISBLANK([Complete to Release (This Month)])),
        Filter(All('Department Hierarchy'[Division]), Not( Isblank([Complete to Release (This Month)]))),
        [Complete to Release (This Month)]
    ,,ASC,Skip)),ALL('Department Hierarchy'[OperationDivision])



This works beautifully on the surface returning the following results.


The issue arises (only sometimes) when a single Division is selected- Compare these views to above:

It's curious to me that the calculation seems to work fine if multiple divisions are selected, but doesn't always work when only one is selected. This is an issue since the design of the report will be for an executive or regional management to slice to a single region/division/location and quickly see how they rank vs others.

Ideally the ranking could be calculated up and down the hierarcy- but that is beyond my abilities at this time.

I'm fairly new to working with virtual tables and PBI in general... I feel like I'm pretty close but am obviously missing something, just not sure what it is.

In case its needed for testing - the slicer is using the hierarchy below


Thanks in advance!!

Community Support
Community Support

Hi @Rbfull ,


Try to replace the ALL function with ALLSELECTED function.

The ALLSELECTED function removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.




Best Regards,

Stephen Tao


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

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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