Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to get multiple results from SWITCH() function?

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:

Langutang_0-1600452368151.png

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

Langutang_1-1600452481972.png

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.

Langutang_2-1600452658016.png

 

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

1 ACCEPTED SOLUTION
zaza
Resolver III
Resolver III

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
)

View solution in original post

2 REPLIES 2
zaza
Resolver III
Resolver III

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
)
Anonymous
Not applicable

This worked like a charm!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors