Here is how you can have a sheet acting like a parameter. This method is useful if you want to have a parameter that dynamically updates - meaning the drop down for the parameter changes with different interactions.
I am going to explain this through an example, so let me start by guiding what the final goal of the example project. This example is using the Sample Superstore dataset.
Goal: Select a Year, Month or Quarter and it will return you the max date of that time period.
On the image below, the parameter on the right will allow you to select "Year", "Month" or "QTR". Depending on the selection, the drop down menu for what you will be able to select are different. Then, if you make a selection on the second step, the final/max date of that time period is plugged into a parameter called "Max Date Param" which you can use for calculations.
The set up for this requires two parameters, two calculations, and a parameter actions.
Here are the parameters:
And here are the calculations:
Now with these created fields, you can set up the view of the sheet as below:
To make it interactive, you need to add this parameter action:
The diagram below shows the dependency of the parameter/fields to the other fields.
- Changing the Period Parameter would change the returned value of the Order Date Dynamic.
- Each Date/Period on the sheet is linked to the appropriate max date for that period
- When the Date/Period on the sheet is clicked, the max date gets populated into the into the Max Date Param
It would also help to sort the order dynamic date by the Order date, shown below.
This way, the most recent Date/Period will be at the top.
Now for this blog post, I decided to populate the Max Date Parameter with the latest date of the period, but if you want to return the period as a string, that is completely possible (easier too!).
Here are the Parameters and Actions you need:
With this set up, now the clicked value will directly go into the parameter!
Overall, you can use this method to have a dynamically updating parameter drop down, by using your sheet as a parameter!