Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How do you write a switch for a field parameter?

I created a field parameter to swap between 6 metrics, shown below. However, I get a strange error when I try to use this in my workbook. Not all columns of the composite key are included in the expression. I guess it expects me to "map it" to my main table, but that's not possible. It's 1 field, to map to 6 columns. How? 

 

Error fetching data for this visual
MdxScript(Model) (211, 23) Calculation error in measure 'Table|Selected Metric Value]: Column [Select a
Metric) is part of composite key, but not all columns of the composite key are induded in the expression
or its dependent expression.

 

 

 

Selected Metric Value = 
SWITCH(
    TRUE(),
    'Metric Selector'[Metric Selector] = "Metric1", SUM('Table'[Metric1]),
    'Metric Selector'[Metric Selector] = "Metric2", SUM('Table'[Metric2]),
    'Metric Selector'[Metric Selector] = "Metric3", SUM('Table'[Metric3]),
    'Metric Selector'[Metric Selector] = "Metric4", SUM('Table'[Metric4]),
    'Metric Selector'[Metric Selector] = "Metric5", SUM('Table'[Metric5]),
    'Metric Selector'[Metric Selector] = "Metric6", SUM('Table'[Metric6])
)

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,
    According to your description, you seem to be experiencing the problem of composite keys, which may be caused by the columns in the filed parameter corresponding to multiple rows in the main table, so we try to reverse pivot the metric in our main table and then write dax, and then, to realize your needs, hope it helps you!

     

    Selected Metric Value = 
CALCULATE(
    SUM('Table'[Value]),
    'Table'[Metric] = SELECTEDVALUE('Table'[Metric])
)

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,
    According to your description, you seem to be experiencing the problem of composite keys, which may be caused by the columns in the filed parameter corresponding to multiple rows in the main table, so we try to reverse pivot the metric in our main table and then write dax, and then, to realize your needs, hope it helps you!

     

    Selected Metric Value = 
CALCULATE(
    SUM('Table'[Value]),
    'Table'[Metric] = SELECTEDVALUE('Table'[Metric])
)

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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