topic Re: How to get multiple results from SWITCH() function? in DAX Commands and Tips

How to get multiple results from SWITCH() function?

Anonymous — Fri, 18 Sep 2020 18:11:07 GMT

Hi All,

I am trying to output multiple results depending on the selected field.

These multiple results are an average, an upper bound and lower bound standard deviation:

This works well as I have put every field into the lines, but I am trying to toggle to share of volume and get the wrong values

These bounds and averages are calculations for the previous metric. So I am trying to make a switch statement for the lines in this visual to select the proper bounds and average depending on the selected metric with the following formula:

Bounds = switch( SELECTEDVALUE(Range[Metric]), "CRT", 'Verizon Call Reason'[YMax CRT], "CRT", 'Verizon Call Reason'[YMin CRT], "CRT", 'Verizon Call Reason'[Avg CRT], "% Share Volume",'Verizon Call Reason'[YMax V%], "% Share Volume", 'Verizon Call Reason'[YMin V%], "% Share Volume", 'Verizon Call Reason'[Avg V%], 0 )

But this only gives me an aggregate of the upper, lower, and average line as something I do not need.

How can I keep these bounds depending on the filter measure I select?

Re: How to get multiple results from SWITCH() function?

zaza — Fri, 18 Sep 2020 22:10:12 GMT

The logic behind switch is this:

if value equals X than "show me this", otherwise

if value equals Y than "show me that", ...

What you've wrote is this:

if value equals X than "show me this", otherwise

if value equals X than "show me that", ...

However this cannot work since X cannot equal 2 conditions at the same time. You cannot have "CRT" 3 times and "% Share Volume" also 3 times.

You need to create 3 separate switch functions:

Upper Bound = switch( SELECTEDVALUE(Range[Metric]), "CRT", 'Verizon Call Reason'[YMax CRT], "% Share Volume",'Verizon Call Reason'[YMax V%], 0 )

Lower Bound = switch( SELECTEDVALUE(Range[Metric]), "CRT", 'Verizon Call Reason'[YMin CRT], "% Share Volume", 'Verizon Call Reason'[YMin V%], 0 )

Average = switch( SELECTEDVALUE(Range[Metric]), "CRT", 'Verizon Call Reason'[Avg CRT], "% Share Volume", 'Verizon Call Reason'[Avg V%], 0 )

Re: How to get multiple results from SWITCH() function?

Anonymous — Fri, 18 Sep 2020 22:15:35 GMT

This worked like a charm!