Dynamic parameter - DAX

[vstefano]

Good morning, 

I am trying to create a dynamic paramenter that displays a different value according the the value selected in a filter (called Role).

The parameter is set up like below.

Desired behaviour: I want that when the value owner is selected and the role is not "DACH Area" then the dimension of the table (based on the parameter) must be "Owner", otherwise (role="DACH Area" and selection = "Owner") the dimension displayed in the table must be Area.

Dimension_Cumulated = {

    ("Owner", IF(SELECTEDVALUE(RolesTable[Role]) = "DACH Area", NAMEOF('Opp Details'[Area]), NAMEOF('Opp Details'[Opportunity Owner])), 0),

    ("TCV € Class", NAMEOF('Opp Details'[TCV € Class]), 1),

    ("Prob. Class", NAMEOF('Opp Details'[Prob. Class]), 2),

    ("Prev.Stage", NAMEOF('Opp Details'[Prev.Stage]), 3),

    ("Area", NAMEOF('Opp Details'[Area]), 4)

}

Somehow it is not working, area is not displaying when the role il selected.

Anyone ca

[DataNinja777]

Hi @vstefano ,

Your issue likely stems from how SELECTEDVALUE(RolesTable[Role]) is being evaluated within the parameter table. If multiple values are selected or no value is selected, SELECTEDVALUE returns BLANK(), which could cause unexpected behavior. Additionally, using NAMEOF inside an IF condition may not work as expected because NAMEOF returns a column reference as a string, but the IF function expects valid expressions.

To resolve this, try defining the logic separately in a measure and then referencing it within your parameter table.

SelectedDimension =
VAR RoleSelected = SELECTEDVALUE(RolesTable[Role])
RETURN
    IF(
        RoleSelected = "DACH Area",
        "Area",
        "Owner"
    )

Then, modify your parameter table to use this measure instead of embedding the IF logic directly.

Dimension_Cumulated = {
    ("Owner", IF( [SelectedDimension] = "Area", NAMEOF('Opp Details'[Area]), NAMEOF('Opp Details'[Opportunity Owner])), 0),
    ("TCV € Class", NAMEOF('Opp Details'[TCV € Class]), 1),
    ("Prob. Class", NAMEOF('Opp Details'[Prob. Class]), 2),
    ("Prev.Stage", NAMEOF('Opp Details'[Prev.Stage]), 3),
    ("Area", NAMEOF('Opp Details'[Area]), 4)
}

By separating the logic into a measure, the role selection will be properly evaluated before being used in the parameter table, ensuring that the correct dimension is displayed based on the filter selection.

Best regards,

[vstefano · Accepted solution]

Thanks DataNinja,

unfortunatly this solution is not working. With DACH Area role and value owner selecting, I still do not get the breakdown by area.

I have found a different solution. See below: 

I added the "Area" dimension to the parameter as number 4.

I have then a middle table between roles and parameter that, based on the role, filter the second table that contains the parameter value (all the roles have values 0,1,2,3 while "DACH Area" have 1,2,3,4.

The middle table is then connected to the parameter via a multi-one relation with filter direction bidirectional, so that I can filter the parameter from the middle table. 

Final results below:

[rajendraongole1]

Hi @vstefano - can you below approach:

Dynamic_Dimension =
VAR SelectedRole = SELECTEDVALUE(RolesTable[Role])
VAR IsDACH = SelectedRole = "DACH Area"
VAR SelectedParameter = SELECTEDVALUE(ParameterTable[Dimension])

RETURN
SWITCH(
SelectedParameter,
"Owner", IF(IsDACH, SELECTEDVALUE('Opp Details'[Area]), SELECTEDVALUE('Opp Details'[Opportunity Owner])),
"TCV € Class", SELECTEDVALUE('Opp Details'[TCV € Class]),
"Prob. Class", SELECTEDVALUE('Opp Details'[Prob. Class]),
"Prev.Stage", SELECTEDVALUE('Opp Details'[Prev.Stage]),
"Area", SELECTEDVALUE('Opp Details'[Area])
)

since you want the displayed dimension to change dynamically based on the selection in the Role filter, a DAX measure.

Hope this helps.