Power BI - Problem, Design, Solution - Concatenated Tooltip

Welcome to a new series that I’m starting with a few colleagues at Pragmatic Works. The goal of this series is to document many design practices and solutions to problems that we often run into with customers. Each post will be accompanied by a video (found in the bottom under Resources) that walks you through the solution in depth. I hope you enjoy this new series!

One feature I am a huge fan of is adding fields to a visualization’s tooltip to help give end users more context to the data they are seeing. Tooltips (as shown below) automatically add fields that are used in the chart, but you also have the option to add additional fields that aren’t used anywhere else in the chart.

Problem

While I do really like this feature, there are also some problems you may run into when using it. Let’s examine the problem:

In this example, I have added the fields Stock and Subcategory from the Fields list. Stock is an implicit measure, which means it is automatically aggregated, and Subcategory, which is a text field.

This works perfectly for Stock because it automatically summarizes the value, but you’ll notice above that the tooltip for Subcategory has an interesting behavior. Rather than displaying the list of the values in Subcategory, it only shows the very first value. This happens because the Tooltip field requires that any column used in it will be able to aggregate or roll up the values into what’s shown on the chart. Since Subcategory is a text field, Power BI automatically applies the FIRST function to return back the first value that appears. You could optionally change this from FIRST to either LAST, COUNT, or COUNTDISTINCT.

So the real problem I want to solve here is, rather than only showing the first subcategory, how do I list all the subcategories in a comma separated list in the tooltip? Let’s walk through a couple of possible designs to this solution.

Design

There are a couple different approaches to solving this problem. One way would be to give every Subcategory a comma separated list. The problem with that is, what if you have 200 subcategories? With 200 subcategories, your tooltip can become even larger than the chart itself. So let’s walk through possible ways of solving this problem. The first method will be a simple design that does not account for the possibility of 200 subcategories. The second method will be a more elegant solution that accounts for a large number of subcategories.

Simple Design

With this simple design, we’ve created a small calculated measure that simply concatenates all the values in the Subcategory column together with a comma separator.

Subcategories = CALCULATE(CONCATENATEX(VALUES(‘Stock'[Subcategory]),’Stock'[Subcategory],“, “))

Let’s break this down:

• The CONCATENATEX function is used here to roll up a list of values. In this case, a list of subcategories into a single row with a comma separator.
• The VALUES function is used to return back a distinct list of subcategories. That way we don’t see duplicate values appearing in our comma separated list.
• Finally, the CALCULATE function is used to set the proper row context here. This ensures the list of subcategories are associated with the correct values in the dataset.

This solution is nice, but not perfect. Let’s look at an even better design.

More Elegant Design

In the more elegant design, we’re accounting for the fact that we could have so many subcategories that the tooltip could possibly take over the report. Essentially, we’re using the same strategies as before, but have incorporated a few new tricks.

Subcategories and More =
VAR SubcategoriesCount = DISTINCTCOUNT(‘Stock'[Subcategory])
RETURN
IF(SubcategoriesCount  >= 3,
CALCULATE(CONCATENATEX(TOPN(3,VALUES(‘Stock'[Subcategory])),’Stock'[Subcategory],“, “))&” and more…”,
CALCULATE(CONCATENATEX(VALUES(‘Stock'[Subcategory]),’Stock'[Subcategory],“, “))
)

Let’s review this design:

• This time a variable is used. This is done to make things a little easier to follow, but not a mandatory part of the design.
• Next, we utilize an IF statement with the intent to check the number of distinct subcategories that are found. The IF statement returns back TRUE or FALSE to the question, “Are there greater than or equal to 3 subcategories?”
  • If the answer to that question is TRUE, then we send it through a process that only returns the top 3 subcategories. It would also return back the text “ and more…” after the top 3 values.
  • If the answer to the IF statement is FALSE, then it would perform the CONCATENATEX function just like we saw in our simple design shown earlier.

Both of these designs solve this problem effectively, but let’s see what the final solution looks like.

Solution

The final step is to pull the entire solution together.  This step is simple because the only requirement is to add either of the two new fields discussed above to the tooltip area of the chart.

In this example, I’ve added the Subcategories and More fields, which check how many distinct subcategories there are, and if there are more than 3, cuts off the list there.

Summary

Leveraging tooltips in Power BI gives you the ability to display additional information about the data your users are looking at.  Leveraging DAX can take this to the next level. In this scenario, we showed how DAX helped to display multiple items in a comma separated list rather than showing the first or last value of that list.

Resources

Video of solution:

• Dataset – Inventory Stock.xlsx
• Completed Example – 01 – Concatenated Tooltip.pbix