Power BI Blog: Dealing with Overflow on Cards

Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau looks at how to deal with an overflow of slicer selections on card visualisations.

This week, we are going to pick up where we left off last week. As a recap, we created a measure that orders the countries by the Total Sales expression:

But what if the user selects more countries than the card can display neatly?

Judging from the current size of the card, it looks like it can comfortably display three countries.  Therefore, we will limit the total number of countries to show in this card to three countries.

Before we create a measure that will do that, let’s take a look at the measure that is being displayed on the card presently:

It works fine on its own, however we want the measure to return with a different result when more than three countries are selected.  In this case, we should use an IF function to distinguish between two actions when a criterion is met.

The measure that I have written might seem a little complicated.  I have split the measure up into two halves in an attempt break it down to explain it.

The first ‘half’ of the measure is as follows:

It reads, if the distinct count of the Country field in the Geography table is less than or equal to three (3), then evaluate using the CONCATENATEX function.

In this scenario, we use the DISTINCTCOUNT to determine the total number of selections made in the Country slicer.  With the following selection,

it will evaluate to four (4).

Now, for the other ‘half’ of the measure, that will be evaluated when the total number of countries selected in the slicer is strictly greater than three (3).

Here we use the TOPN function to determine the total number of countries to be concatenated.  You can read more about the TOPN function here.

Moving up a level in the measure, we then have a similar layout for the CONCATENATEX function where:

• the table is determined from the TOPN function
• the expression is Geography[Country]
• the delimiter is “, ”
• the orderby_expression is [Total Sales]
• the order is set to DESC or descending.

Moving one more level up in the measure, we have to nest the CONCATENATEX function with a CONCATENATE function to include an “, etc…” at the end of the string.

When we put both parts of the measure together we get this:

List of Countries Overflow =

Placing this measure onto a card visualisation should get this result:

There we have it, how to manage overflow in cards displaying slicer selections.

That’s it for this week! Come back next week for more on Power BI!

In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.