Top N Filter in a Matrix

[j_ocean]

I have a matrix within which I need to show only the top n items within each category based on a sum calculated in a measure.

Just using the top-n visual level filter on the [row heading] using the [value measure] gives me top-n globally, not within the next hierarchy level up. 

I used this code and got the rank numbers to work right, but when I add any visual level filter, even one just hiding blank values, it bizarrely recalculates not just the rank column but the sums. I tried replacing ALLSELECTED with ALL, no luck.

[j_ocean · Accepted solution]

I solved the issue by eliminating from the visual hierarchy levels from other dimension tables. It was creating an internal many:many relationship. I do not understand why it should matter when those levels were not being expressed, but this fixed the problem.

[AlB]

Hi @j_ocean 

Can you share the pbix? If necessary with dummy data but reproducing the issue

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[j_ocean]

Not really, I'd have to make a completely new PBIX just for this. Plus there doesn't seem to be an attachment function...

Here's the applicable bits of the schema though:

Dimension table:

Category A

ID Number 1 to many, one way filter direction

Fact table:

ID Number  many to 1

Category B

Value Measure (a simple sum with a filter)

Output I Need:

Category A 1

     Category B 1         Measure Value      Rank 1

     Category B 2         Measure Value      Rank 2

Category A 2

     Category B 3         Measure Value      Rank 1

     Category B 2         Measure Value      Rank 2

I'm currently trying  https://www.daxpatterns.com/ranking/  which returns the value vs ranking which I'll take for now but causes a memory overflow when trying to filter so I don't know if it's doing the same weird recalculation.

[V-lianl-msft]

Hi @j_ocean ,

Be aware that DAX measure are based on row context so any given value from slicers, filters and so on can influence your result.

A pbix with dummy data can let us know your scenario better.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.