Because of not sorting a filter in descending order, I added a numbering column partitioned by weekly in descending order to see the recent week on top of the filter and the recent figures on the table. However, even if the data are refreshed and the week-numbering has changed, it remains the former numbering and the figures aren’t showing up on the table.
e.g) Let today be 21st Oct, 2024, and the week numbering of 21st Oct is ‘001’ and make the calculated column is ‘001.21st_Oct_2024’ and I set up the ‘001.21st_Oct_2024’.
One week later, it’s 28th Oct and the week numbering of 28th Oct is ‘001’ and 21st Oct is ‘002’. But the data don’t show up on the table visual because it was set up in the ‘001.21st_Oct_2024’ on the filter and data are already changed.
So, I have to try the new method right now. → Which is ‘interactions-actions’ properties.
Before I do select on the ‘on select Table’, the sum of all figures is shown on the Visual Table. This isn’t what I wanted. And I set up the Filters =‘001’ in a red box so that I could see the recent figure on the Visual Table every time. But the problem is, it doesn’t work if I click another week on the ‘On-select Table’ because of the filter=‘001’ in the red box.
I believe you should be able to do this with parameters set to relative dates and parameter actions.
Could you create an arena link with an anonymized view of your data? That would help me understand how this will work in your use case and using your data:
Hello iamboram.
To achieve your goals—displaying the most recent values by default on a visual table and dynamically updating them when a different week is selected—here’s a strategy you might consider. This involves using a combination of calculated fields and parameters with interaction-based actions:
Step 1: Default Display of the Most Recent Week
Calculated Field for Week Ranking: Ensure that your weekly ranking is dynamically updated to always reflect the most recent week as “001.” In many BI tools, you can use functions like ROW_NUMBER with a descending order on the date column to assign these ranks dynamically.
Default Filter for Recent Week: Set a filter to display only records where the calculated rank (e.g., “001”) corresponds to the most recent week. This filter should automatically adjust each time data is refreshed, keeping the most recent week on top.
Step 2: Dynamic Update on User Selection
Parameter and Calculated Field for Interactions: Create a parameter that will hold the user’s selection from the “On-select Table.” This allows you to pass the selection value (such as the selected week) into your calculated fields.
Interaction-Based Filter Logic: Use conditional logic in your calculated fields. For example, if the parameter has a value, use it; otherwise, fall back on the default recent week (“001”). Here’s a simplified logic structure:
Replace UserSelectionParameter with the interaction-driven parameter value and MostRecentWeek with the default ranking value (e.g., “001”).
Dynamic Table Filter: In your table visual, set up a filter to display records based on the calculated field above. This way, when there’s a new user selection, the parameter is updated, and the table reflects the change. If no selection is made, it defaults to the most recent week.
Implementing the Parameter in BI Tool Settings
Since each BI tool (like Power BI, Tableau, or AWS QuickSight) has slightly different configurations, here’s how to check:
Enable Interactions with Parameters: Ensure that the “Interactions-Actions” property is set to pass values as parameters in your BI tool. In Power BI, this would be the Selection Control or Parameter Actions in Tableau.
Setting Parameter as Filter: Once the interaction updates the parameter, apply this as a filter on your visual. This setup should work around the restriction you experienced with hardcoding specific weeks and will allow the default display while supporting user interactions.