KNOWLEDGE BASE

Display dimension members as a list rather than as individuals rows or marks


Published: 03 Sep 2015
Last Modified Date: 19 Aug 2019

Question

How to display all members of a dimension as a list rather than individual rows or marks. For example concatenating sub-categories into one comma separate list for each category. 

Environment

Tableau Desktop

Answer

Option 1: Use table calculations in Tableau Deskop

Step 1: Create the Calculations

  1. Create a calculated field with a name like "Create the list" with a calculation similar to the following:
    IF FIRST()= 0
    THEN MIN([Sub-Category])
    ELSE PREVIOUS_VALUE("") + ", " + MIN([Sub-Category])
    END
  2. Create a calculated field with a name like "Sub-Category List Label" with a calculation similar to the following:
    WINDOW_MAX([Create the list] )

    Edit calculated fields in the attached workbook for additional notes about how these calculations work.

Step 2: Create the Bar Chart View

  1. Drag [Category] and [Sub-Category] to the Rows shelf
  2. Drag [Sales] to the Columns shelf
  3. Drag [Sub-Category] to Color on the marks card
    Note: [Sub-Category] must be included in the view for the calculations to work correctly. If the final view should not include the dimension being listed in the tooltip, see the "Adv Option 1" tab for additional instructions
  4. Drag [Sub-Category List] to Tooltip on the marks card
  5. Right-click [Sub-Category List] on Tooltip and select Compute Using > Sub-Category
Note: If additional dimensions are added to the view, then it may be necessary to update the Compute Using setting. For detailed explanation of how to control the output of a table function with Compute Using settings, please see Transform Values with Table Calculations.

Option 2: Use Tableau Prep

  1. Add a step
  2. Right-click [Sub-Category] in Clean 1 and select Duplicate Field
  3. Add Aggregate
  4. In Aggregate 1, do the following:
    1. Add Category to Grouped Fields
    2. Find Sub-Category in the list of Additional Fields, click GROUP, and select Minimum (This will add MIN(Sub-Category) to Aggregated Fields)
    3. Right-click MIN(Sub-Category) in Aggregated Fields and select Rename. Rename it something like "Sub-Category (first)"
    4. Add MAX(Sub-Category-1) to Aggregated Fields
    5. Rename MAX(Sub-Category-1) to something like "Sub-Category (last)"
  5. Drag Aggregate 1 over Clean 1 and drop Aggregate 1 on Join
  6. Add a step from Join 1
  7. In Clean 2, create a calculated field named "Filtered Sub-Category" with a formula like
    IF [Sub-Category] != [Sub-Category (first)]
    AND [Sub-Category] != [Sub-Category (last)]
    THEN [Sub-Category]
    END
  8. In Clean 2, duplicate [Filtered Sub-Category]
  9. In Clean 2, remove [Category-1] and [Sub-Category]
  10. Repeat steps 3-9 to create as many fields as needed. In this example, the category with the most sub-categories has 9 sub-catgories, so the prep flow needs 5 aggregate steps to create 10 fields so no sub-categories are missed.
  11. In the last Clean step, create a calculated field named something like "Grouped sub-categories" with a formula like:
    IF ISNULL([Sub-Category (first)])
    THEN ''
    ELSE [Sub-Category (first)] + ' '
    END
    
    + IF ISNULL([Sub-Category (second)])
    THEN ''
    ELSE [Sub-Category (second)] + ' '
    END
    
    + IF ISNULL([sub-category (third)])
    THEN ''
    ELSE [sub-category (third)] + ' '
    END
    
    + IF ISNULL([sub-category (third to last)])
    THEN ''
    ELSE [sub-category (third)] + ' '
    END
    
    + IF ISNULL([Sub-Category (second to last)])
    THEN ''
    ELSE [Sub-Category (second to last)] + ' '
    END
    
    + IF ISNULL([Sub-Category (last)])
    THEN ''
    ELSE [Sub-Category (last)] + ' '
    END

    The above calculation combines all of the sub-categories fields made. If the sub-category field has a NULL value, then that NULL must be replaced with an empty string (aka two quote marks '') because combining a value and NULL value always results in NULL.
  12. Output the data
Did this article resolve the issue?