I have an example .pbix file if anyone is interested.
Imagine a table
If you create a Visual Level Filter that say 'filters for active = Y' and displays a count. And you apply a Top n filter (ie Top 2 by Sales)
You would expect the count to be 1 (the row with Rep c) but the card in PowerBI will show a count of 2 because it ignores the Top 3 filter.
Here is a screenshot of the example:
I figured out a workaround. Create first as a table and then change to a card. It allows the Top N filter to be applied to a different field then what a card will accept.
Based on my research, when you add a visual level filter for active = Y and Topn N filter, the visual will be filtered filter rows for "Y" then calculate Top N for the filtered rows to return valid data. Personally think Count 2 is expected.
In your scenario, would you please clarify the workaround "I figured out a workaround. Create first as a table and then change to a card. It allows the Top N filter to be applied to a different field then what a card will accept."?
Best Regards,Qiuyun Yu
Please ignore my workaround. I realise now that creating a table and adding a field also makes the field filterable in the Visual Level Filter by TopN.
Adding a field to a card creates a summarised 'count' of that field which can'd be filtered by TopN. If you add the field again to the Visual Level filter then you can add the TopN filter. Something I thought creating first as a table then converting to a card was doing but clearly you could do without converting back and forth.
Where I think this is not working, is that if I have:
- 13 are Active and
- 87 are Inactive
When I add a Top 30 Filter with a Count on Active. Should I get 13? Or should I get the result after the Top 30 are applied?
It is a bug in my thinking so please close this out and ignore. Hopefully by putting it here other people can resolve their own problem.
My problem was I am thinking of a visual level filter on a card looking for the state of a value rather than what it is actually doing which is filtering the values that are being used to calculate the value of the card.
Sum of Active in the Top 3 (gives 2)
Sum of Active in the Top 3 that are Active (gives 3)
Originally I had this problem because I was trying to get a card to count the number of TRUE values in a card. Because I can't sum TRUE (assuming True would be 1) then I thought there was a bug.
This stuff is so hard to explain so sorry if it is confusing.