Top N Filter in Visual Level Filter ignored if other filters are present

[jaykilleen]

I have an example .pbix file if anyone is interested.

Imagine a table 

Rep|Sales|Active

a|100|Y

b|200|N

c|300|Y

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.

[v-qiuyu-msft]

Hi @jaykilleen,

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 

[jaykilleen]

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.

[jaykilleen]

Where I think this is not working, is that if I have:

100 values

 - 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?

[jaykilleen]

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.