topic Re: Rank changes as soon as I apply visual level filter to filter out TopN in DAX Commands and Tips

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

Anuj_Nnada — Mon, 09 Dec 2024 00:01:22 GMT

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())

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

Anuj_Nnada — Mon, 09 Dec 2024 01:04:14 GMT

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

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

tharunkumarRTK — Mon, 09 Dec 2024 03:27:32 GMT

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!

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

Anuj_Nnada — Mon, 09 Dec 2024 03:45:21 GMT

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?

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

tharunkumarRTK — Mon, 09 Dec 2024 13:02:48 GMT

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!

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

Anonymous — Mon, 09 Dec 2024 14:03:16 GMT

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

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

Anuj_Nnada — Mon, 09 Dec 2024 15:46:44 GMT

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?

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

Kedar_Pande — Mon, 09 Dec 2024 16:14:44 GMT

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

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

Anuj_Nnada — Mon, 09 Dec 2024 16:26:24 GMT

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 -

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

tharunkumarRTK — Tue, 10 Dec 2024 02:16:07 GMT

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!

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

Anuj_Nnada — Wed, 11 Dec 2024 18:13:34 GMT

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.

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

Anonymous — Fri, 13 Dec 2024 14:03:24 GMT

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

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

Anonymous — Wed, 18 Dec 2024 12:17:01 GMT

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

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

Anonymous — Fri, 20 Dec 2024 12:21:07 GMT

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

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

Anonymous — Thu, 26 Dec 2024 09:25:56 GMT

Hi @Anuj_Nnada,

Regards,

Vinay Pabbu