Forum Discussion

More_BI's avatar
More_BI
Frequent Visitor
9 years ago
Solved

Switch function breaking my chart

Hey guys,   I use the switch function in various measures to carefully toggle how the KPI will be implemented. For example, I have a column called "Rollup Type" which is tied to the list of KPIs. I...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi More_BI,

     

    Try to change your measure to something like this:

     

    a_test = SWITCH(MAX('KPI List'[Rollup Type])
             "SUM", CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual"), 
        "EOB", CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual"))

     

    Regards,

    MFelix

More_BI's avatar
More_BI
Frequent Visitor
9 years ago

Yup, that worked. But why was my original measure causing multiple values? I want to better understand how this function works. Thanks!

MFelix's avatar
MFelix
Super User
9 years ago

Hi More_BI,

 

The VALUES formula returns a list of values( see link with explanation) so you when you use it to return a single value to your visuals it will give you the error you have, since in the DAX measure the context of the measures are import for the final results, usually values is used in the filters or to sum other values.

 

Regards,

MFelix

  • More_BI's avatar
    More_BI
    Frequent Visitor
    9 years ago

    Interesting... that makes sense. So the values function overrrides the switch function, is that correct? 

     

    If I understand the switch function, it should return one value.

     

    How is the max function working here? Does it only get one result back from the switch and therefore will always be the right value?

    • MFelix's avatar
      MFelix
      Super User
      9 years ago

      Hi More_BI,

       

      As part of the argument on the SWITCH since the VALUES return more than one value the SWITCH will have an error, when you use MAX or MIN you are returning only one value so complying with the arguments needed in the SWITCH.

       

      Regards,

      MFelix