Dynamic selection of dimensions in pivot table

Question & Answer
, , ,
1

Hey Everyone,
I have a use case where I wish to list down all the dimensions for the user in a form of filter
and the user can choose which ever and how much ever dimensions to add to the pivot table.

Is this possible, by any chance on Quicksight???

2

Are you looking to do a filter? or a control based off another visualization?

For the later, you could have a visualization that is a grid that shows all your dimensions. Then you can create a control off that grid and use the selection of any row of the grid to filter the data of a second visualization.

For the former, you could just create a parameter and filter that is added to the sheet that is a multi-select. The filter would be targeted to the visualization in question.

3

Hey,
In the filed wells of the pivot table, I need to drop all the dimensions at which I wish to see the values.
example

In this case the dimensions in the pivot table are date, page and platform, my concern is that if the user wishes to add more dimensions to this pivot from their end further drill down platform on a dimension of their choice.

Is that possible???

4

Hey @Jesse ,@emilyzhu
Please help me with the above case.

5

Hi Ummehaani,

You can do this 1 field at a time using Parameters and a Calculated Field to give the user a control to pick the field they want to add. You would need 1 control per field though (cannot use a single, mutli select control to add several). See this example to learn more (if you mouse on the right you can open the analysis and reverse engineer it to see the calculations and parameters used).
https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Interactivity-Dynamic-Dimensions-and-Measures

We are looking at adding this ability for Readers to pick a set of columns to add/remove from a table in the future.

3 Likes
6

Hey @Jesse ,
This works perfectly fine.
The only struggle with this is the output field can be either a date type or a metric or a dimension…We can’t have a mixture of field types as options for the output field in the ifelse.

7

Hi Jesse,

Any update on this feature yet? I would love to provide my report users the ability to build their own table by choosing the dimensions they would want to see for their analysis. Having a multi-select control in this case to choose would be fantastic.

Thanks!

8

Sorry no updates yet but this is still an item the product team is tracking.

9

This is exactly what I was looking for. Thank you for such a detailed response!

1 Like
10

Hi @Jesse ,

But I believe, with that method, the user can select the calculated field that wants to see, but only one per time. Is there a way to implement a solution that the user can multiselect those fields from a dropdown list, adding as much values as the user wants to use?

Thanks!

11

Correct they cannot multiselect fields from a single dropdown using this method.

1 Like
12

Hi @Jesse
I still don’t get this example… my requirement is add/remove column to pivot table based on user selection. here we are just assign a column to calculated dimension, thus number of dimensions shown in the dashboard is always constant… but I want to increase or decrease based on parameter control

13

Hi @Ananth - correct, this solution allows a user to swap a dimension with any other one, but does not allow you to add/remove columns from a table using a single control. If you had a parameter, control and calculated field for each column you want in the table then they can select multiple, but not from a single control. For that we would need to embed the dashboard in a page and write some custom code to add and remove columns entirely.

1 Like
14

Hi @Jesse , Thank you very much for clearing that for me. Our end goal is to embed the dashboard to our website. I saw few of the document on how to embed dashboard to a webpage, but could you point me to more embed documentation on how to add the custom code you mentioned

15

@Ananth sure, Ive asked one of our specialists who created a solution for Tables (not Pivot Tables) to share some guidance. He is going to test it on Pivot Tables first. Give us a little time but we will update with some guidance soon.

2 Likes
16

Hi @Jesse, Could you show us the tables examples? we will also check if we can apply same to pivot tables

17

Hi @Ananth
Please find below the link for tables using dataset parameters.
https://dm4j7qn6bn5ut.cloudfront.net/qs-tips-tricks/index.html?dashboard=fb35f128-9091-4055-baf0-9563909cddea&sheet-sheet&account=newfeature

For multiselect, I followed the below community solution:
https://community.amazonquicksight.com/t/how-can-i-select-multiple-lines-from-a-table-and-how-could-i-then-deselect-those-items-to-exclude-them-form-the-analysis/1217/6

Here the maximum number of fields that you can select cannot change but based on the selection you make on the left table, only the relevant number of fields get populated in the order of selection. Though this is based on direct query, you can modify it for SPICE as well.

Please let me know if this end solution works for you. If so, I can share the detailed steps.

Regards,
Vetri

18

Hi, can anyone share the detailed steps regarding the process? I’m trying to follow through with a similar goal for my analysis but I’m new to BI platforms and still understanding the flow. Thank you!