Filter by Rank measure

Hi J94 
Your ranking is evaluated based on the visible rows in your visual so when you visually filter it by a measure value, the rows being evaluated naturally change. Try this pattern

Category and Model Rank by Sales 2 = 
VAR RankingTable =
    CALCULATETABLE (
        ALLSELECTED (
            'Product'[Category],
            'Product'[Model]
        ),
        REMOVEFILTERS ( 'Product' )
    )
RETURN
    RANKX (
        RankingTable,
        [Sales],
        ,
        DESC,
        DENSE
    )

Notice that in the image below, the second table is filtered to show rows with sales less than 100K but Rank2 still says the same as the first table without a filter where   as Rank has changed values.

Please see the attached pbix.

Hi User,

The behavior you are seeing is usually related to how Power BI evaluates measure filters in the filter pane.

Your RANKX measure is being calculated correctly inside the table visual because Power BI evaluates the ranking row by row. However, when you apply the measure as a visual filter, the filter context changes, and the ranking can be recalculated differently. This can cause only a subset of the expected Top 10 items to remain.

A few things you can try:

1. Create the ranking measure without changing the context too aggressively

Try:

Orders vs Forecast % Rank =
RANKX(
    ALLSELECTED(dim_product_attributes),
    [Orders vs Forecast %],
    ,
    DESC,
    DENSE
)

Using the entire dimension table in ALLSELECTED can preserve the correct context better than specifying only two columns.

2. Use the rank filter directly

Instead of creating:

TopN10equalTo1 =
IF([Orders vs Forecast % Rank] <= 10, 1, 0)

Add the rank measure directly to the visual filter:

Orders vs Forecast % Rank is less than or equal to 10

Then set the value after the visual is fully configured.

3. Check for ties

Because you are using:

DENSE

multiple products can receive the same rank. For example, if several products have the same percentage at rank 10, the result can include more than 10 rows. If you need exactly 10 rows, you may need a secondary sorting column.

4. Validate blank values

If [Orders vs Forecast %] returns BLANK() for some products, RANKX behavior can become unexpected. You can test with:

Orders vs Forecast % Rank =
IF(
    NOT ISBLANK([Orders vs Forecast %]),
    RANKX(
        ALLSELECTED(dim_product_attributes),
        [Orders vs Forecast %],
        ,
        DESC,
        DENSE
    )
)

Another approach is to use a Top N filter on the visual:

• Select the ProductLine/ModelNPIDesc field

• Filters pane → Filter type: Top N

• Show items: Top 10

• By value: [Orders vs Forecast %]

This often performs better because Power BI handles the ranking internally.

For more information:

• RANKX function: https://learn.microsoft.com/dax/rankx-function-dax

• ALLSELECTED function: https://learn.microsoft.com/dax/allselected-function-dax

• Top N filters in Power BI: https://learn.microsoft.com/power-bi/create-reports/service-top-n-filters

Could you also confirm:

• Are ProductLine and ModelNPIDesc from the same dimension table?

• Are there slicers affecting the visual (date, region, customer, etc.)?

• Do you need exactly 10 rows, or should ties at rank 10 also be included?

That will help determine the best ranking approach for your model.

Hi Prince0011 

thanks for your suggestion. Unfortunately, it doesn't work. I did try the same calculation as your Orders vs Forecast % Rank before, but the ranking in the table isn't correct anymore (see screenshot). Maybe it has to do with the Orders vs Forecast % measure, where the RankX measure is based on:

Orders vs Forecast % =

VAR _orders = [QuantityOrder excl. IC]

VAR _invoiced = SUM(fact_salesinvoices[QuantityInvoiced])

VAR _forecast = Salesforecast_FTP[Sales Forecast_Measure]

 

RETURN

IF(

    NOT ISBLANK(_forecast),

    DIVIDE( COALESCE(_orders + _invoiced, 0), _forecast, 0 ),

    BLANK()

)
To be complete, here are the two other measures:

QuantityOrder excl. IC =

CALCULATE(

    SUM(fact_salesorderlines_open[QuantityOpenToInvoice]),

    dim_relation_master[IsInterCompanyDesc_edw] = "No"

)

Sales Forecast_Measure = //When there is no Forecast in the past, use SUM QuantityInvoiced 

SUMX(

    VALUES(dim_date[YearMonthNumber_og]),

    VAR _forecast =

        CALCULATE(

            SUM(Salesforecast_FTP[Sales Forecast])

        )

    VAR _monthIndex =

        CALCULATE(

            MAX(dim_date[MonthIndex])

        )

    RETURN

        IF(

            _forecast = 0

                || _monthIndex < 0,

            CALCULATE(

                SUM(fact_salesinvoices[QuantityInvoiced])

            ),

            _forecast

        )

)

 

The problem is likely the results returned by ALLSELECTED. When you include either of the measures in the visual then ALLSELECTED does what you expect it to, returning the values which are visible in the filter context after removing filters generated by the current visual. However, when the measure is only used as a filter, and is not included in the visual itself, the measure is calculated in a different filter context and the values returned by ALLSELECTED are not what you would expect.

I think that the answer is to include the [Orders vs Forecast % TopN] measure in the visual but hide it. You can either temporarily add a visual calculation, hide the TopN measure and delete the visual calculation, or you could set the column width to 0 and turn word wrap off for that column.