This has happened to me a few times recently – and it got me thinking about what’s going on. Let me explain with a demo.
Let’s say we’ve built a simple Power BI report showing NCAA football game results from the 2020 season. In this report we’ve got a simple formula to capture the count of games played.
CountofGames = COUNTROWS(Games)
We’ve put this measure in a Card visual at the top of the report – as well as in a table visual for purposes of this exercise.
The ask is simple – filter the table and card so that they only show games played by teams in Conference “ACC”.
This “ACC” value can appear in two different columns of the games table, either home_conference or away_conference.
While it may be advisable to reshape this data – let’s just run with it and write a measure using an “OR” statement. Recall that after the March 2021 Power BI update – the “OR” operator can now be used with Calculate:
ACC Flag = CALCULATE ( COUNTROWS ( Games ), Games[home_conference] = "ACC" || Games[away_conference] = "ACC" )
This formula returns the correct value of 85:
Now we are going to use this [ACC Flag] measure to filter the table visual. As expected – this visual level filter will change the table to only show the 85 games amongst ACC teams.
It’s reasonable to assume that if we can apply this filter to the table visual – we should also be able to apply it to the card visual for Count of Games. However, when we try to do this – the drop down buttons in the filter selection pane simply stop working.
What’s going on here…..?
Well… it’s all about context.
Specifically how aggregations and totals work in Power BI and what information is available in the context of the visual for the formula or filter to work with.
A card visual is just one value – and we can think of it like the TOTAL cell of the table visual.
Infact, if we remove all the columns from our table – except for the CountofGames column – it would essentially act exactly the same way as a Card.
And – when we reduce the table visual to one column – we can see that even though the visual filter is still applied – it no longer works as expected. It has lost its context.
If we start adding columns back – we can get an idea of which columns are required to make this filter work in the first place. Adding columns for Season and Week Number are no help….
If we add just the home team column to the table – the total is close but also not quite right either.
So then at minimum we need columns in the table that impart context for BOTH the away team and home team in order for our visual level measure-based filter to return to correct value. Adding both gives us the correct answer (85).
So if we extend this logic to the card visual – it should now be easy to see why the option to use our measure is impractical. There is no context for it to work.
So then is the option to filter any card with a measure disabled in Power BI
We can get around this by converting our table visual (with the filter applied) to a card visual – and while the filter remains in place – it’s not working as expected.
Of course for those who find themselves trying to filter the card visual with a measure – there is a simple work around: just put the measure directly in the card! (Didn’t we do that already?)
It’s an easy fix, and we now have a better understanding of what’s going on under the hood.