How to Hide Legend Measure for Missing Data in a Dual Axis Chart

Published: 05 Sep 2023
Last Modified Date: 06 Sep 2023

Question

When data is missing as shown below, a color legend for the missing data is displayed when a dual axis chart is created. How do we hide the empty measure item from the legend?

User-added image

Environment

Tableau Desktop

Answer

Follow along in the sample packaged workbook found in the Attachments section to hide the legend item that has missing data. In our example, the Profit for the year 2023 has no data.

1. Add MONTH(Date) to Columns.
2. Add SUM(Sales) and SUM(Profit) to Rows.
3. Right-click SUM(Profit) in Rows shelf and select Dual Axis.
4. Open SUM(Sales) on the Marks card and change the mark type to Bar.
5. Create a calculated field and name it Color for Sales with the following formula:

IF NOT ISNULL([Sales]) THEN STR(YEAR([Date]))+" Sales" END

6. Create a calculated field and name it Color for Profit with the following formula:

IF NOT ISNULL([Profit]) THEN STR(YEAR([Date]))+" Profit" END

7. Open SUM(Sales) on the Marks card and add Color for Sales to Color.
8. Open SUM(Profit) on the Marks card and add Color for Profit to Color.
9. Right-click the Null literal in the Color for Profit legend and select Hide.

User-added image