How to create a dynamic default value for a control

Hi, I’m looking to set a dynamic default value for a control.

I created a parameter and control for a string field, and I’d like to set a default value for that control. I’d like the default value to be the max value in the control list, meaning it would be dynamic. With every new dataset update, the max value in the string field will change, and thus in the control list. I’d like the control list’s new max value to be the new default value.

Here’s an example:
I have a string field that has a list of dates in the MM/DD/YYYY format. (I did not make the data type of this field “Date” and kept it a “String,” because QuickSight does not show the aforementioned date format I’d like). Upon making this a control, the control list looks like this:

With the next update of the dataset, a later date will show (e.g. 06/01/2023). I would like the new default value of the control to be 06/01/2023, considering it will be the new max value.

I understand that I can set a static default value but this would require a manual change every time the dataset is updated. I would like this to be dynamic, and the latest date (max value) would automatically show.

Hopefully the question’s clear. Thank you!

  1. First of all, you’ll have to change the field to a date field. If you leave it as a string, QuickSight can’t determine the max. If you have issues with the date format, we can address that separately. I see you’re using MM/dd/yyyy - that should not be a problem in QuickSight.

  2. Then create a copy of the dataset and use it to calculate the max value at the dataset level, e.g. by using SQL if your data source is a database. You also need to include a column that contains every reader’s UserName or GroupName.

  3. Create a parameter in your analysis and click on “Set a dynamic default”.

  4. Select the dataset from step #2, the column that contains the UserName or GroupName, and the column that contains your maximum value.

1 Like

@David_Wong Hello
I have the exactly same scenario but the above is not working for me.
I have created a parameter, Set the dynamic default to the field in the dataset which is having a single value (max of date).
In the control, I have selected it as dropdown and selected another field which displays all the dates I have in dataset.
The control shows the dropdown of all dates but the default selected is ALL in the dropdown. I want that to be max value which I have set in the parameter

Hi @Sam1,

When you set your dynamic default, did you select the column which contains the username or group name?

This is not working for my case as well. I even selected the username and group. Still when I show the control the dashboard we need to select again, by default it is showing select all. Please Help.

1 Like

It doesn’t seem to work even after selecting the user name and group name columns. It shows ‘ALL’ as the value. I want to display a single default value. Do you have any suggestions?

1 Like

@David_Wong

Isn’t possible to obtain the max if the string is YYYY/MM/dd ?

Also if the default value does not depend on the user, how can I set the dynamic value?

How can I obtain the my username and groupname when accessing the dashboard?

@isabelapt,

You can’t use max on a string value in QuickSight but you can convert it to a date first and then use max on it. Can you convert your YYYY/MM/dd string values to dates in your dataset?

If the default value doesn’t depend on the user, what does it depend on?

Are you trying to find your own username or the username of your users? Your username is what you use to log in to the QuickSight console. If you’re an admin, you can find all the usernames by going to Manage QuickSight > Manage Users. You can find the group names by going to Manage QuickSight > Manage Groups.

@David_Wong Yes, I can convert the dates to strings. The default value should rely on the maximum date present in the data at the moment. Every time a new date is inserted into the dataset, the default value should change and be recalculated.

I am not an admin; is there any way to figure it out?

Hi @David_Wong , I’ve converted the string to date format. Every week, the dataset is incremented, and new values related to that date are inserted. I would like to add a filter based on this date and set the default to the maximum one, but users should be able to change it to another if they want to.

Hi @isabelapt,

You need to create a dataset that looks like this and use it to set the dynamic default value of your parameter. There’s one row for every username in QuickSight. Use SQL to find the max date in your dataset and add it to every row of the dataset.

UserName MaxDate
Username1 2025-02-05T00:00:00.000Z
Username2 2025-02-05T00:00:00.000Z
Username3 2025-02-05T00:00:00.000Z

If you have a group containing all your users, you can set the max date for the entire group.

GroupName MaxDate
Group1 2025-02-05T00:00:00.000Z

@David_Wong
I just want to set dynamic default for a parameter.
For example, year of value 2015 to 2025, I want to default to 2025 since current year is 2025.
Next year, when it is 2026, list of values will be 2016 to 2026 and I want to default to 2026.

This is standard for everyone. Why do I need to have Username and Group?

PowerBI has the ability to just default based on current date/time.
Can QuickSight provide similar feature?

Thanks.