Monday Minis: Show/Hide Labels Parameter

You can find the accompanying workbook to this post here.

It’s pretty common for a client to request all the labels to be shown on a chart because while they want to see trends (let’s say it’s a line chart) they also want to know the exact values for each mark on the chart. Now, you may say to the client, “Well, you can hover over the chart and get the exact values in the tooltip.” Or perhaps you suggest, “We can also provide a table with all values you can reference.” However, I’ve come across clients that want to be able to print the view with the specific labels and therefore need to have them on the chart or simply just want to see all the values at once. The problem is – it’s a very cluttered view when all the labels are on the chart, particularly when the chart has a lot of marks.

I have a solution for you – a show/hide labels option using a parameter drop-down. This easy trick allows your client or user to see a clean chart without labels or see the chart will all the labels! Win-win! Another great benefit of the parameter is that you can add in some other options such as just labeling the minimum and maximum value, or perhaps only the most recent values in a time-series chart.

You can check out the full details and viz in my downloadable visualization here.

The Build

To create this you’ll need a parameter that has all the string values you want as options for your user. In my example, I chose the following: no labels, all labels, min and max, and the last 5 values. You can choose whatever makes sense for your user.

Next, create a calculation that will label based on the parameter selection. This calculation will be placed on the text marks card on your chart. Note that for “No Labels” we are using null values so that no text shows up, so that parameter selection will not be included in the calculation.

To show all labels, we simply write the following using the field we have in our view:

IF [Show Labels]=’Show All’ THEN SUM([Profit])

Next, we add in the Min/Max labels:

ELSEIF [Show Labels]=’Min/Max’ THEN
(IF SUM([Profit])=WINDOW_MAX(SUM([Profit])) or SUM([Profit])=WINDOW_MIN(SUM([Profit])) THEN SUM([Profit]) END)

This will be true only if the value is the minimum or the maximum within the window. This allows the calculation to be dynamic if filters are applied to the worksheet.

Lastly, we add our “show the last 5” portion of the calculation. This one is a bit trickier, but as mentioned, you can add in any type of dynamic labels you want such as first/last values, or every 5, etc. To do this we find the max date value in our view and then go back 4 months so that we can isolate the last 5 points.

ELSEIF [Show Labels]=’Last 5′ THEN
(IF WINDOW_MAX(MAX(DATETRUNC(‘month’,[Order Date])))>=ATTR(DATETRUNC(‘month’,[Order Date]))
AND ATTR(DATETRUNC(‘month’,[Order Date]))>=DATEADD(‘month’, -4,WINDOW_MAX(MAX(DATETRUNC(‘month’,[Order Date])))) THEN SUM([Profit]) END)
END

The basic premise is that you create sections of the calculation that isolate marks based on some condition and therefore is only true when those conditions are true. This will then only show labels when true and nothing when false. Keep in mind that the WINDOWS function requires aggregation. This means that when I used the Date field, I had to use the ATTR() function in a few places to get the Date field to be an aggregation.

You then can add this parameter to your view either in a dashboard or buttons or a worksheet and use parameter actions. In my example, I just am showing the default drop-down menu, which I’ve made very small so only the arrow shows up, thus keeping the parameter out of the way and right in line with the title of the worksheet. This creates a great user experience in my opinion too.

I hope this Monday Mini tip was helpful and useful!

If you have questions, feel free to reach out!

Cheers,

Lindsay