In a Pivot Table. Is there any way to hide / show entire rows of values based on a control dropdown

I have a bunch of Calculated Fields which have been grouped by a column and I need the ability to hide / show the entire row of values , not just make the values NULL

Hi @KshitijBlazeclan -

You can use a cross-join and a dimension dataset to accomplish this. This workaround will result in what’s called a “join explosion” where every row of your data is multiplied by the number of values in your dimension dataset. The amount of data you are working with will determine how practical this solution is.

Gotchas:: You will not be able to show different units/percents unless you convert all your values into strings.

Alt Solution: 1 If you are open to splitting the groups into separate visuals and a free-form layout, you could use Showing and hiding pivot table columns in Amazon QuickSight - Amazon QuickSight .

Step 1 - Create a “link” column in your source dataset. The value should be a constant and will have a matching column in the next dimension dataset.
image

Step 2 - Create your dimension dataset
image

Step 3 - Cross-join your datasets using the Link column

Step 4 - Create a calculated field that will switch the values depending on the dimension selected.

{cf_name}='cf2',col2,
{cf_name}='cf3',col3,
{cf_name}='cf4',col4,
null
)

Example:
2022-07-12_14-18-34 (1)

3 Likes

Great, Thanks for this. Worked out just the way I wanted it.

1 Like