Rankx function is behaving weird when used in table with extra columns and Slicers

[cruncher]

Hi Experts.

I am having a hard time understanding dax evaluation context in Rankx function. I want to rank the customers based on sales. These are the steps I have followed.

Step 1: Creating an Measure Sales: SUM(Sales[Sales])
Step 2: Add Table to visual. Add Customer Name and Sales Measure. Sort Visual by Sales.
Step 3: Create Customer Rank : Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),Sales[Sales_M],,DESC,Dense)
Step 4: Drag Customer Rank to Table visual
Step 5: Add visual level filter and include only Rank Source= Yes
Step 6: Add a single select slicer based on Source and remove blank from slicer list. Select any one source.
Step 7: Show the Rank by respecting both Visual level filter and Slicer.

Upto Step 7 everything is working fine and rank is also correct.

Step 8: Drag Category to Table and rank is incorrect now. I want to show Category as passive field without affecting the rank.
Step 9: Changed Rank calculation and context to exclude Category from Filter context.
Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),CALCULATE(Sales[Sales_M],REMOVEFILTERS(Sales[Categoy])),,DESC,Dense)

Rank is correct now

Step 10: Add Status to table. Now rank is incorrect again. Changed rank context again to remove Status from filter context
Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),CALCULATE(Sales[Sales_M],REMOVEFILTERS(Sales[Categoy],Sales[Status])),,DESC,Dense)

Rank is correct now

Step 11: Add Status to slicer. Remove Unknow from Slicer values so that it doesn't appear in slicer select list and then filter on one status.

Now data is filter for that status but Rank is not continuous like 1,2,3,5. It first calculated the rank and then filtered on status.

I want rank to respect the Status filter when applied in slicer but status should act as passive field in the visual.

Once Rank is correct then I need a Top N parameter where user can select Top N customers by respecting all slicers

Here is pbix file. PBIX 

Thank you in advance for help.

Regards,
Cruncher

[v-kathullac]

Hi @cruncher  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[v-kathullac]

Hi @cruncher  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[cruncher]

It didn't work. Please try with pbix file I attached earlier

[v-kathullac]

Hi @cruncher ,

can you check with below mentioned measure and let us know if you need any further assistance. for your reference am attaching a screenshot as well.

Customer_Rank 1 = 
VAR SelectedStatus = VALUES(Sales[Status])
RETURN
RANKX(
    FILTER(
        ALLSELECTED(Sales),
        Sales[Rank Source] = "Yes" &&
        Sales[Source] IN VALUES(Sales[Source]) &&
        Sales[Status] IN SelectedStatus
    ),
    CALCULATE(SUM(Sales[Sales])),
    ,
    DESC,
    DENSE
)

Regards,

Chaithanya.. 

[cruncher]

Thanks for trying but it still not giving correct rank.

[v-kathullac · Accepted solution]

Hi @cruncher ,

Can you try with below dax measure attaching the screenshot for your reference.

Customer_Rank 3 = 
RANKX(
    FILTER(
        ALLSELECTED(Sales),
        Sales[Rank Source] = "Yes"
            && Sales[Source] IN VALUES(Sales[Source])
    ),
    CALCULATE(SUM(Sales[Sales])),
    ,
    DESC,
    DENSE
)

Regards,

Chaithanya.

[cruncher]

Thanks @v-kathullac It is working as expected. Much appreciated for your effort.

[v-kathullac]

Hi @cruncher ,

can you try with below dax measures.

Create Sales Measure : 

Sales_M = SUM(Sales[Sales])

Initial Rank Measure

Customer_Rank =
RANKX(
ALLSELECTED(Sales[Customer]),
[Sales_M],
,
DESC,
DENSE
)

Make Category Passive in Rank

Customer_Rank =
RANKX(
ALLSELECTED(Sales[Customer]),
CALCULATE([Sales_M], REMOVEFILTERS(Sales[Category])),
,
DESC,
DENSE
)

Make Status Passive in Rank (Breaks Slicer)

Customer_Rank =
RANKX(
ALLSELECTED(Sales[Customer]),
CALCULATE([Sales_M], REMOVEFILTERS(Sales[Category], Sales[Status])),
,
DESC,
DENSE
)

Final Rank Measure – Respects Slicer, Ignores Visual Filters

Customer_Rank =
RANKX(
FILTER(
ALLSELECTED(Sales[Customer]),
CALCULATE(MAX(Sales[Status])) <> "Unknown" // optional
),
CALCULATE([Sales_M], REMOVEFILTERS(Sales[Category])),
,
DESC,
DENSE
)

Enable Dynamic Top N Filter
1.Create Top N Parameter (from Modeling)
Table_Name: TopNValue
Range: e.g., 1–20

ShowTopN =
IF([Customer_Rank] <= [TopNValue], 1, 0)

Apply Visual-Level Filter : Drag ShowTopN to visual-level filter and Set it to show when value = 1

Regards,

Chaithanya.

[v-kathullac]

Hi @cruncher  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[cruncher]

Hi @v-kathullac I am still facing issue with showing correct rank when used with Top N calculation and john was not able to figure it out

[v-kathullac]

Hi @cruncher  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[johnt75]

For the ranking you need to use ALLSELECTED( Sales[Status] ), not REMOVEFILTERS.

Customer_Rank =
RANKX (
    ALLSELECTED ( Sales[Customer] ),
    CALCULATE (
        Sales[Sales_M],
        REMOVEFILTERS ( Sales[Categoy] ),
        ALLSELECTED ( Sales[Status] )
    ),
    ,
    DESC,
    DENSE
)

For the TopN filter you can create a numeric parameter and then create a measure like

Row Is Visible =
IF ( [Customer_Rank] <= [Top N Parameter Value], 1 )

Use this as a filter on the visual to only show when the value is 1.

[cruncher]

Thanks @johnt75 for looking into it. If I use your new rank calculation then it is not respecting the status slicer and Unknown is showing in the status in the table for Microsoft customer.

[cruncher]

Thanks for clarfication. It helps.

Now. I created a Topn numeric parameter from Modelling tab and used this calculation to filter the top n customers.

ShowTopNOpps = IF([Customer_Rank]<='Top N'[Top N Value],1,0)

I used this in Table visual filter and selected 1. Now Rank is again incorrect.

[johnt75]

can you upload the latest version of the PBIX ?

[cruncher]

Here is the latest PBIX 

[johnt75]

I think I understand what the problem is but unfortunately I don't know how to fix it.

Because there is a filter on the ShowTopNOpps measure the query generated for the table visual contains a SUMMARIZECOLUMNS which calculates the rows which should be visible in the final result. I think that this SUMMARIZECOLUMNS is generating a new shadow filter context, which is what is accessed by ALLSELECTED. This seems to be messing up the measure calculation.

Given that there is no way to control the DAX query generated for the table visual, there is no way to get around this.

The only thing that I could suggest is instead of using a parameter and measure to control the top N, use a Top N filter in the filter pane, ranking Customer by the customer ranking measure.

[cruncher]

Thanks for insights. I need a slicer in my view so I can't use the Top N filter. I will wait for someone else to chime in. A few questions if you can help:

1. How you're testing which filter are getting generated by dax queries like treatas, summarize as you shared in this post.

2. When to use the third parameter in rank. What is the significance of that and if there any performance benefit

3.  Can we use TOPN dax function to filter the top n customer instead of RankX to overcome this issue ?

4.  From where I can get advance knowldege of this evaluation context transitions. Its very confusing when to use which dax.

[v-kathullac]

Hi @cruncher ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Below are the few Points that can solve your issue.

1) How you're testing which filters are getting generated by DAX queries like TREATAS, SUMMARIZE?

• Use DAX Studio to view effective filters, query plans, and server timings.
• Use Performance Analyzer in Power BI to see which DAX queries visuals are generating.
• Create debug measures using: CONCATENATEX(VALUES(Table[Column]), Table[Column], ", ")
• Use ISFILTERED, HASONEVALUE, VALUES to detect if a column is being filtered

2) When to use the third parameter in RANKX? What's the significance and any performance benefit?

• Third parameter is used when the ranking expression returns a blank.
• Acts as a fallback value, often set to 0 or -1.
• Ensures blanks still receive a rank (e.g., customers with no sales).
• Provides more predictable ranking behavior.
• Minor performance impact; mostly used for logic consistency.

3) Can we use TOPN DAX function to filter the top N customers instead of RANKX?

• Yes, TOPN returns a table of top N values based on expression.
• Works well in calculated tables or virtual tables for visuals.
• Example with slicer: VAR N = SELECTEDVALUE(TopN[Value]) RETURN TOPN(N, VALUES(Customer[CustomerName]), [TotalSales], DESC)

4) Where can I get advanced knowledge of evaluation context transitions?

• Learn from sqlbi.com – highly recommended for DAX and model optimization.
• Read articles on context transition, row context vs. filter context.
• Use DAX.do for browser-based testing and DAX learning.

Regards,

Chaithanya.

[johnt75]

That seems to be the behaviour of the Select All  option in the slicer. If you untick Select All and then select either Booked or Unbooked both then it works. With Select All selected the filter generated for the underlying DAX query is different. 

If you do not tick the Select All option and choose Booked then the filter generated is

VAR __DS0FilterTable3 = 
    TREATAS({"Booked"}, 'Sales'[Status])

If you tick the Select All option and then untick Not Booked the filter is

VAR __DS0FilterTable3 = 
	FILTER(
		KEEPFILTERS(VALUES('Status'[Status])),
		NOT('Status'[Status] IN {"Not Booked"})
	)

The second version, with Select All ticked, checks that the Status is not "Not Booked", but this ignores the filter that you put on the slicer to not show Unknown.

You should raise this as a bug with MS support.

As a workaround, you may need to disable the Select All option.