Forum Discussion
Filter Order issue with TOP N usage
I will do my best to share sample data but this is hard to explain. I am attempting to have a Bottom 5 performers visual (my top 5 works fine BTW) and it seems the filter to prevent people with "Adjusted Commitment" = 0 is being apllied AFTER the TOP N filter so the visual is just blank. How do I get the filters to change order? I need the bottom 5 performers by "% Commmitment" (a column for data) where their "Adjusted Commitment" does not equal 0 (which means they didn't have a requirement that day and so should be excluded).
I will include as many snips as I can.
SAMPLE DATA
| Date | Team | Pod | Name | Production | Adj. Commitment | % Commitment |
| 9/19/25 | Corr | Dogs | Tom Jones | 10 | 10 | 100.00% |
| 9/19/25 | Corr | Cats | Eve Smith | 0 | 0 | 0.00% |
| 9/19/25 | Corr | Cats | James Pots | 5 | 10 | 50.00% |
| 9/19/25 | Corr | Dogs | Penny James | 2 | 10 | 20.00% |
As you can see, the TOP N works, but I suspect the TOP N for the Bottom 5 is executing before the filter for Adj. Commitment so its selecting people with 0 commitment then those people are being excluded resulting in a blank.
I think I included as much as I can, I need a solution, I know I can't tell PBI to change it's filter order manually, but is there a work around?
Thank you so much for any help you can provide.
Cheers,
Lauren
Apologies for the delay. I have reworked the visuals so this is no longer a problem, but have never actually solved why the visual had gone blank. Thank you for your attempts to help me though it is hugely appreciated!
6 Replies
When I take the sample data, I am getting expected results.
I have it set to bottom 2 (without the filter on 0 just yet to ensure it is doing what we want) and I see Eve and Penny
When I add in the filter for is not 0, it changes to James and Penny, which is expected:
Can you ensure that there are rows where the role is Production and adjusted committment is not 0?
There are most definitely rows. The data set invovles hundreds of people, most of them are with a normal commitment. I can't give the data as it is private, nor can I generate an accurate false version of the data (time constraint). I know it's not the most helpful, my filter theory is just a theory but it's the only thing that makes sense.
Hi LYorkToenniges ,
Thanks for posting in Microsoft Fabric Community and for sharing the additional details.
I tested this with sample data where some rows have Adjusted Commitment as zero and others have valid values.
First, with a Bottom N filter on % Commitment only, the visual returns the lowest values including rows where Adjusted Commitment is zero:
After adding a filter to exclude zero Adjusted Commitment, the Bottom N recalculates and shows the expected result:
This confirms that in a clean dataset, the combination of Bottom N and the exclusion filter does not result in a blank visual.
If the visual is still blank in your case, please check if the Adjusted Commitment column is numeric and does not contain blanks, nulls, or text values that may be excluded differently.
Also verify if other filters such as Role = Production or page/report level filters are further restricting the dataset.
As a workaround, you can either create a calculated column that blanks out % Commitment when Adjusted Commitment is zero and then use that column in the Bottom N filter, or use a measure with RANKX that applies the exclusion before ranking so only valid rows are considered.
I am attaching a sample PBIX file with both approaches implemented so you can review the behavior directly.
Hope this helps. Please reach out for further assistance.
Thank you.
Hi LYorkToenniges ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.Apologies for the delay. I have reworked the visuals so this is no longer a problem, but have never actually solved why the visual had gone blank. Thank you for your attempts to help me though it is hugely appreciated!
