Rank changes as soon as I apply visual level filter to filter out TopN

[Anuj_Nnada]

Hi, I have Ranking measure which is working fine and giving me the correct ranking based on different column in my Table

Rank =

IF (

    HASONEVALUE ( Dim_Professionals[ProfessionalKey] ) && [Sales] > 0,

    RANKX (

        ALLSELECTED ( Dim_Professionals ),

        CALCULATE (

            [Sales],

            ALLSELECTED( Dim_Territory[Territory]),

            ALLSELECTED( Dim_Segments[Segment])

        ),

        ,

        DESC,

        DENSE

    )

)

Problem is now I want to filter out my table absed on Top10 or Top20 Selection as soon as I use the below dax in the visual level filter and select "1", my Rank measure start giving me different result. How can I solve this problem and not make it change the result while using a visual level filter?

VAR TopNN = SELECTEDVALUE(RankFilter[Top Value])

RETURN

IF(HASONEVALUE ( Dim_Professionals[ProfessionalKey] ) && [Sales] > 0 && [Rank]<=TopNN,1,BLANK())

[Kedar_Pande]

@Anuj_Nnada 

Fix the Ranking Measure:

Rank =
IF (
HASONEVALUE ( Dim_Professionals[ProfessionalKey] ) && [Sales] > 0,
RANKX (
ALLSELECTED ( Dim_Professionals ),
CALCULATE (
[Sales],
ALLSELECTED( Dim_Territory[Territory] ),
ALLSELECTED( Dim_Segments[Segment] )
),
,
DESC,
DENSE
)
)

Top N Filter Measure:

TopNFilter =
VAR TopNN = SELECTEDVALUE ( RankFilter[Top Value], 10 ) // Default Top Value
RETURN
IF ( 
HASONEVALUE ( Dim_Professionals[ProfessionalKey] ) && [Rank] <= TopNN, 
1, 
BLANK() 
)

Use the TopNFilter measure as a visual-level filter on your table and set it to "is not blank." This ensures the visual shows only rows with ranks less than or equal to the selected TopNN value, without affecting the evaluation of the Rank measure.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anuj_Nnada]

I have already tried this. Problem is that as soon as I pull Territory and Segment in my table it changes the ranking while the visual level filter is set to "is not blank." I hope you undertsand!

When Visual Level fiulter applied -

When Visual level filter not applied - 

[v-vpabbu]

Hi @Anuj_Nnada,

Thank you for using the Microsoft Fabric Community!

The rank is dynamically recalculated whenever the context changes (e.g., after applying the filter to show only TopN rows). The ranks are recalculated within the filtered context, which leads to the rankings being different than expected.

You need to ensure that the rank is calculated over the entire dataset and not affected by the filter applied to the visual. The ranking should be computed as if there were no filters at all on the dataset.

Remove the visual filter's effect from the ranking measure by using ALL in the RANKX function, which ignores the applied filters and ranks over all available data.

Apply the filter only to display the top N rows based on the calculated rank.

If this steps works please accept this as solution. If you need a more detailed explanation, kindly share the PBIX file along with sample data.

Regards

Vinay Pabbu

[tharunkumarRTK]

@Anuj_Nnada 

ALLSELECTED function will observe visual level filters, since you applied TOPN visual level filter which is an external filter, it will apply that filter and recalculate rank value. 

lets say you added "[RankMeasure]  greater than 10" visual filter then it will remove topn 10 professional key and then calculates then rank, so you rank measure values in the visual will start from 1. 

I would suggest you to first remove the visual filter and add the Filter measure that you created to apply the visual level filter into the visual and see if there any issues. 

If you need any further help then please share the PBIX file with sample data and expected result. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Anuj_Nnada]

I understand what you're trying to say but how can I make my Rank not get affected by the visual level filter? I am already using the filter measure and using it in Visual level filter and only filtering those records that which have rank <=10. Is there any way we can make sure that Rank measure ignore the visual level filter?

[tharunkumarRTK · Accepted solution]

@Anuj_Nnada 

Ideally if you are just filterin topn then it shoud not effect your rank numbers. for example if you see the below image

Rank 
RankFunction = RANKX(ALLSELECTED( Customers[CompanyName] , Customers[CustomerID]), [Revenue], ,DESC,Dense) 
-------------------
Visual filter 

RankFunction2 = 
var maxValue = max(topntable[value])
return if([rankfunction] <= maxvalue,1,0) 

However if you filter out topn then it will cause the problem

RankFunction2 = 
var maxValue = max(topntable[value])
return if([rankfunction] >= maxvalue,1,0) 

 

If filtering topn is only the requirement then you can use this pattern 

 

TopnFunction = 
Var __N = MAX(topntable[Value])
Var __TopnTable = TOPN(__N, ALLSELECTED(Customers[CompanyName], Customers[CustomerID]), [Revenue], DESC)
Var __Result = CALCULATE([Revenue], KEEPFILTERS(__TopnTable))
RETURN __Result 

For further assistance please share your PBIX file with sample data and expected result.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Anuj_Nnada]

I agree! Even I think of filtering the Dataset first using TOPN Dax function. However, the problem with TOPN is that it is not considering ties and is giving me random no. of rows. Please find attached the screenshot below! 

When I filter Top 10 - It gives me 10 Rows

when I filter Top 20 - It gives me 34 rows

Measure Used - 

TopN =

CALCULATE([Sales],

KEEPFILTERS(TOPN(20,ALLSELECTED(Dim_Customer), [Sales],DESC)))

Why does it give some random number of rows?

[tharunkumarRTK]

@Anuj_Nnada 

I dont think it is giving random number of rows. It is filtering all the rows which falls under the rank filter criteria, i.e less than 20. 

From what I understand, you need ROWNUMBER function which gives unique rank based on the criteria. 
please go through this 
https://dax.guide/rownumber/

After creating the measure, within the measure you can add a condition.
if (rownumber < tonnfilter, rownumber, blank())

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Anuj_Nnada]

I agree with your point but the Rownumber function doesn't consider Ties so in my case it will not help as I have data with ties.

[v-vpabbu]

Hi @Anuj_Nnada,

Some DAX queries, particularly those using the RANKX function, may have limitations as it dynamically adjusts to filters applied, including visual-level filters. This behavior can result in unexpected changes to rank values.

For reference : RANK function (DAX) - DAX | Microsoft Learn

Please let me know if you have any other queries.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @Anuj_Nnada,

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,

Vinay Pabbu

[v-vpabbu]

Hi @Anuj_Nnada 

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @Anuj_Nnada,

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,

Vinay Pabbu

[Anuj_Nnada]

Just realised, it is filtering the table correctly as per sales but visually changing the ranking. Which is super weird!