dynamic valuelist

[ali_hijazi]

Hello
I got a pivto table with Fiscal_Year as a dimension
so we have the following values:
2023
2024
2025

I created another dimension as follows:
ValueList(dual('Actual',1), dual('Budget',2), dual('FCS#1',3) , dual('FCS#2',4))

so these values will be repeated for each fiscal year above
however what I want is that:
I have 3 filter panes each in an alternate state:
so if user selects Actual in the first filter pane then under 2023 I should only display Actual and thus calculate the actual values
if in the second filter pane the user selects Actual, and Budget then under 2024 I should display both actual, and budget and thus calculate the actual and budget values repsectively
...
kindly advise on how to accomplish this or if there is another approach via which I can accomplish this requirement

[marcus_sommer]

I think it's "only" a question of querying all parts within a nested if-loop, like:

if(Year = X and Valuelist() = Y and SelectionState1 = Z, Expr1,
if(Year = X and Valuelist() = Y and SelectionState1 = A, Expr2,
...

which could become quite ugly and rather slow by a bigger data-set and/or complex calculations.

Personally I would tend to avoid such calculated dimension as well as the use of n states and creating an appropriate dimensional-layer within the data-model which may end in using Year + KPI as native dimensions and sum(Value) as expression.