topic Re: Detail Table Won't Filter Automatically for Top N, Even When Grand Total is Correct in DAX Commands and Tips

Detail Table Won't Filter Automatically for Top N, Even When Grand Total is Correct

shabeercp659 — Tue, 10 Jun 2025 15:46:45 GMT

https://drive.google.com/drive/folders/1B4J-i9eTyTEmBX-c_AZd1OlyzR9gby4W

Hello Power BI Community,

I am facing a very persistent issue with a Top N report that seems to defy the normal behavior of DAX.

My Goal:
I need a report with two table visuals and a slicer for 'N'.
1. Table A should list the Top N travelers and their total spend.
2. Table B should automatically show the detailed transactions for ONLY that Top N group.

What I Did:
I have built a perfect data model from scratch with a clean data sample.
- A unique `Travelers` dimension table (the "1" side).
- A `Data` fact table (the "many" side).
- A correct 1-to-many, single-direction relationship.
- Two DAX measures:
- `Is Top Traveler`: Uses RANKX to identify the Top N (returns 1 or 0).
- `Top Traveler Spend`: Uses TOPN/KEEPFILTERS to calculate spend for only the Top N (returns BLANK for others).

How the Report is Built:
- Table A is filtered by `[Is Top Traveler] = 1`. This works perfectly.
- Table B is filtered by `[Top Traveler Spend]` is not blank. This is the part that fails.

The Problem:
In Table B, the rows do not filter. It continues to show all travelers. However, the grand total at the bottom of the table is correct and shows the sum for only the Top N travelers.

What We've Checked:
- Interactive filtering (manually clicking a name in Table A) works, so the relationship is correct.
- Visual interactions are set to "Filter".
- "Show items with no data" is turned off.
- We have tried this in a brand new file, and the problem persists.

Request:
I uploaded the PBIX file in a Google drive and shared the link above. Can someone please help ?

Thank you for your help.

Re: Detail Table Won't Filter Automatically for Top N, Even When Grand Total is Correct

johnt75 — Tue, 10 Jun 2025 15:38:43 GMT

You can post a link to the PBIX on Google Drive, OneDrive or similar.

Re: Detail Table Won't Filter Automatically for Top N, Even When Grand Total is Correct

shabeercp659 — Tue, 10 Jun 2025 15:47:18 GMT

I have uploaded in a google drive and the link is now pasted in the original post

Re: Detail Table Won't Filter Automatically for Top N, Even When Grand Total is Correct

johnt75 — Wed, 11 Jun 2025 08:49:04 GMT

You need to remove the filters from the ticket id column,

Top Traveler Rank = VAR SelectedN = [Top N Selection Value] VAR TravelerRank = RANKX( ALL(Travelers), CALCULATE([Total Amount], REMOVEFILTERS( Data[Ticket Number] )), , DESC, DENSE ) RETURN TravelerRank

You'd need to do the same thing in the Is Top Traveller measure, and then add [Is Top Traveller] to the detail table as a filter, to only show when the value is 1.

Re: Detail Table Won't Filter Automatically for Top N, Even When Grand Total is Correct

v-lgarikapat — Mon, 16 Jun 2025 07:29:14 GMT

Hi shabeercp659 ,

Thanks for reaching out to the Microsoft fabric community forum.

johnt75 ,

Thanks for your prompt response

shabeercp659 ,

I wanted to check if you had the opportunity to review the information provided by johnt75 , Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" so other members can easily find it.

Best Regards,

Lakshmi Narayana

Re: Detail Table Won't Filter Automatically for Top N, Even When Grand Total is Correct

shabeercp659 — Mon, 16 Jun 2025 13:17:04 GMT

Thanks johnt75. This was super helpful!