Sample Dashboard: Finding Max Value Path
I have a data table that contains multiple dimension columns and a measure column called “Value.” My users need to dynamically select a combination of these dimensions to group by and sum the “Value” column. The goal is to find the combination of dimensions that yields the maximum sum of the “Value” column across all possible combinations.
To illustrate this scenario, I have created a dashboard above that manually demonstrates the process for just three dimension combinations. However, this approach becomes impractical as the number of dimensions increases, and a more dynamic solution is required.
Ideally, I would like to explore a dynamic approach to solve this problem, either through calculated fields in the QS where the calculation path is recalculated based on the applied filters, or by creating a new dataset with a different structure.
One potential solution could be to pivot all column values into a single new dimension and then simply select the top 1 result. But this covers a path length of 1 only.
Another approach that comes to mind is to utilize a recursive Common Table Expression (CTE) in an SQL statement. However, this method might limit the ability to calculate based on user-applied filters, which is a desirable feature in this scenario.
The crux of the problem lies in finding an efficient and dynamic solution that can handle varying numbers of dimensions and user-applied filters while still identifying the combination that yields the maximum sum of the “Value” column.