topic Re: Rankx with Top N driving me nuts. Please help in DAX Commands and Tips

Rankx with Top N driving me nuts. Please help

cruncher — Tue, 20 May 2025 22:13:56 GMT

Hi experts,

I am having a hard time finding the correct rank with TopN and filters/slicers in matrix view. My data looks like this.

These are my requirements.

1. Filter the data where Rank=Yes.

2. Create a Single select Parameter from source field excluding any blanks values.

3. Filter the data to respective source as per user selection.

4. Rank by Region and sales.

5. Add Rank, Region, Category, Include, Sales in Table/Matrix view

6. Add Include field to filters. Rank should be calculated after filtering the data.

7. Create a Top N type in parameter and show regions Rank less than Top N value.

I tried multiple things by changing the context using All and All selected, Remove filters, Calculate but not able to filter the correct topn 1 ranks after the filter/slicer selection.

Here is the pbix file with sample data PBIX link

Please help. Many thanks in advance. Appreciate if someone invest some time to solve this.

Regards,

Ombir

Re: Rankx with Top N driving me nuts. Please help

adudani — Tue, 20 May 2025 23:07:07 GMT

hi cruncher ,

Does this resolve the issue?

Re: Rankx with Top N driving me nuts. Please help

maruthisp — Wed, 21 May 2025 02:56:47 GMT

Hi cruncher,

As always DAX is harder to understand when compared with writing SQL queries. I would suggest below for you:
1.First come up with a sql query to retrieve the result as per your business logic.
2.Second thing is translate the same logic in to DAX expression
3.It will be easy to understand SQL and DAX simultaneouly how it works and behave.
4. As you know there is no single solution for problem to solve. You can come up with multiple approaches to solve the same problem.
5. Be cool 😉
Rankx.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X

Re: Rankx with Top N driving me nuts. Please help

cruncher — Wed, 21 May 2025 18:46:46 GMT

Thanks for looking into it. It returns wrong rank.

Can you check and also include Top N parameter to filter the rank

Re: Rankx with Top N driving me nuts. Please help

cruncher — Wed, 21 May 2025 18:56:28 GMT

Thanks for looking into it. It returns wrong rank and Top N is not working. Please check

Re: Rankx with Top N driving me nuts. Please help

v-echaithra — Thu, 05 Jun 2025 09:11:31 GMT

Hi cruncher ,

Thank you for the update.

Sales Rank =
VAR _selectedSource = SELECTEDVALUE('Source Parameter'[Source])

VAR _topN = SELECTEDVALUE('Top N Parameter'[Value])

VAR _filteredTable = FILTER(ALLSELECTED(Sheet2), Sheet2[Include] = "Yes"&& Sheet2[Rank] = "Yes"&& Sheet2[Source] = _selectedSource)

RETURN
IF (Sheet2[Include] = "Yes" && Sheet2[Rank] = "Yes"&& Sheet2[Source] = _selectedSource,
RANKX(_filteredTable,[Sales $],,DESC,DENSE))

,and a measure to filter top n results

Show_Top_N =
VAR _topN = SELECTEDVALUE('Top N Parameter'[Value])
RETURN
IF([Sales Rank] <= _topN, 1, 0)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thank you.

Re: Rankx with Top N driving me nuts. Please help

v-echaithra — Mon, 09 Jun 2025 06:43:40 GMT

Hi cruncher ,

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,
Chaithra.

Re: Rankx with Top N driving me nuts. Please help

v-echaithra — Thu, 12 Jun 2025 08:56:41 GMT

Hi cruncher ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

Re: Rankx with Top N driving me nuts. Please help

FBergamaschi — Fri, 13 Jun 2025 13:39:20 GMT

Hi cruncher, I am not sure I got what you need, can you please clarify on my below questions?

1. Filter the data where Rank=Yes.

2. Create a Single select Parameter from source field excluding any blanks values.

3. Filter the data to respective source as per user selection.

FB what do you mean in point 3.? Having a slicer with regions to be selected?

4. Rank by Region and sales.

FB Please clarify what you need to rank and by which value (es Rank Regions by Sales )

5. Add Rank, Region, Category, Include, Sales in Table/Matrix view

6. Add Include field to filters. Rank should be calculated after filtering the data.

7. Create a Top N type in parameter and show regions Rank less than Top N value.

Re: Rankx with Top N driving me nuts. Please help

v-echaithra — Mon, 16 Jun 2025 06:31:38 GMT

Hi cruncher ,

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,
Chaithra.