Field Parameter composite key error?

[Data_Analyst905]

I'm facing an issue in Power BI after creating a field parameter to swap between 4 hierarchy columns with person names in them. The user should be able to select hierarchy level in slicer, and then, the associated column is returned. But I get error noted below.

Error fetching data for this visual

MdxScript(Model) (79, 20) Calculation error in measure Swap Test]: Column

[hierarchy_parameter_testl is part of composite key, but not all columns of the composite key are

included in the expression or its dependent expression.

Here's what my 'field parameter' table shows in the DAX after creation using Modeling > New Parameter > Fields

hierarchy_parameter_test = {
    ("DIRECTOR_NAME", NAMEOF('FIELD_PARAMETER'[DIRECTOR_NAME]), 0),
    ("MANAGER_NAME", NAMEOF('FIELD_PARAMETER'[MANAGER_NAME]), 1),
    ("SUPERVISOR_NAME", NAMEOF('FIELD_PARAMETER'[SUPERVISOR_NAME]), 2),
    ("VP_NAME", NAMEOF('FIELD_PARAMETER'[VP_NAME]), 3)
}

Here's the swap to change to each of my original table columns:

Hierarchy Swap Test = 
VAR selectedRole = SELECTEDVALUE(hierarchy_parameter_test[hierarchy_parameter_test])
RETURN
SWITCH(
    selectedRole,
    "VP", MAX('TABLE1'[VP_NAME]),
    "DIR", MAX('TABLE1'[DIRECTOR_NAME]),
    "MGR", MAX('TABLE1'[MANAGER_NAME]),
    "SUP", MAX('TABLE1'[SUPERVISOR_NAME])
)
 

So in short, I have a field parameter to change across 4 simple columns, but the Switch calculation won't allow me to do so, telling me there's a composite key missing. What am I missing here? I feel like this should be so simple. Pick a value, return the column!

[v-kongfanf-msft]

Hi @Data_Analyst905 ,

Try to modify your formula like below:

Hierarchy Swap Test = 
VAR selectedRole = MAX(hierarchy_parameter_test[Parameter])
RETURN
SWITCH(
    selectedRole,
    "VP_NAME", MAX('TABLE'[VP_NAME]),
    "DIRECTOR_NAME", MAX('TABLE'[DIRECTOR_NAME]),
    "MANAGER_NAME", MAX('TABLE'[MANAGER_NAME]),
    "SUPERVISOR_NAME", MAX('TABLE'[SUPERVISOR_NAME])
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Data_Analyst905]

@v-kongfanf-msft this only returns 1 value, my intention is for it to pick an entire column of values. So user picks slicer value 1: Column 1. Slicer value 2: column 2 and so on. Using MAX only returns 1 value, not the entire list of associated values

[DataInsights]

@Data_Analyst905,

To use SELECTEDVALUE with field parameters, adjust your DAX per the article below:

https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/ 

Another option is to use MAX:

VAR selectedRole = MAX ( hierarchy_parameter_test[hierarchy_parameter_test] )

[Data_Analyst905]

Hi @DataInsights - Thanks for sharing that resource article. Had a look, and maybe I am missing something, but Max will only return 1 value, rather than the entire list of values in the column. So when I pick a level, it returns just 1 name, instead of the entire list of values. 

[DataInsights]

@Data_Analyst905,

Have you tried using the field parameter in the visual? The engine is able to dynamically use the selected field without the need for custom DAX. You can customize the names as shown below:

hierarchy_parameter_test =
{
    ( "DIR", NAMEOF ( 'FIELD_PARAMETER'[DIRECTOR_NAME] ), 0 ),
    ( "MGR", NAMEOF ( 'FIELD_PARAMETER'[MANAGER_NAME] ), 1 ),
    ( "SUP", NAMEOF ( 'FIELD_PARAMETER'[SUPERVISOR_NAME] ), 2 ),
    ( "VP", NAMEOF ( 'FIELD_PARAMETER'[VP_NAME] ), 3 )
}