Forum Discussion

osuraperera86's avatar
osuraperera86
Regular Visitor
3 years ago

Nested Switch Function

I'm trying to calculate a dissatisfied value based on two criteria, Name and Year. Below is the formula I've written. For some reason, the values for 2019 and 2020 don't generate. Can anyone here help me? 

Highly Dissatisfied - Summary =

VAR TotalResponses =

CALCULATE(
    COUNT('Combined Raw Data'[Rating Value]),
    'Combined Raw Data'[Rating Value]<>blank(),
    'Mapping Level 1'[Service Area]="Overall",
    'Mapping Level 1'[Overall/Area]="Overall",
    'Combined Raw Data'[Rating  / Comment]="Rating"  
)

VAR HighlySatis =

CALCULATE(
    count('Combined Raw Data'[Rating Value]),
    'Combined Raw Data'[Rating Value]=1||
    'Combined Raw Data'[Rating Value]=2||
    'Combined Raw Data'[Rating Value]=3||
    'Combined Raw Data'[Rating Value]=4,
    'Mapping Level 1'[Service Area]="Overall",
    'Mapping Level 1'[Overall/Area]="Overall",
    'Combined Raw Data'[Rating  / Comment]="Rating"
)

Return
switch(
    SELECTEDVALUE('Combined Raw Data'[SL ABB]),
    "GBS",
    switch(
    SELECTEDVALUE('Year For Summary'[Year]),
    2019,0.0747,
    2020,0.0408,
    2021, Divide(HighlySatis,TotalResponses),
    2022, Divide(HighlySatis,TotalResponses)
    ),
    "FIS",
    switch(
    SELECTEDVALUE('Year For Summary'[Year]),
    2019,0.1562,
    2020,0.0890,
    2021, Divide(HighlySatis,TotalResponses),
    2022, Divide(HighlySatis,TotalResponses)
    ),
    "MDS",
    switch(
    SELECTEDVALUE('Year For Summary'[Year]),
    2019,0.1592,
    2020,0.1051,
    2021, Divide(HighlySatis,TotalResponses),
    2022, Divide(HighlySatis,TotalResponses)
    ),
    "WSS",
    switch(
    SELECTEDVALUE('Year For Summary'[Year]),
    2019,0.1652,
    2020,0.1304,
    2021, Divide(HighlySatis,TotalResponses),
    2022, Divide(HighlySatis,TotalResponses)
    ),
    "HRS",
    switch(
    SELECTEDVALUE('Year For Summary'[Year]),
    2019,0.1513,
    2020,0.0944,
    2021, Divide(HighlySatis,TotalResponses),
    2022, Divide(HighlySatis,TotalResponses)
    ),
    "CAS",
    switch(
    SELECTEDVALUE('Year For Summary'[Year]),
    2019,0.1261,
    2020,0.0701,
    2021, Divide(HighlySatis,TotalResponses),
    2022, Divide(HighlySatis,TotalResponses)
    ),
    "SAS",
    switch(
    SELECTEDVALUE('Year For Summary'[Year]),
    2019,0.2105,
    2020,0.1413,
    2021, Divide(HighlySatis,TotalResponses),
    2022, Divide(HighlySatis,TotalResponses)
    )
)

2 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

    This code has a lot of repetitions. Please tidy this up, make it shorter, remove repetitions. Instead of || use the IN operator. It's not possible to tell you where the problem is without actual data.

    • osuraperera86's avatar
      osuraperera86
      Regular Visitor
      3 years ago

      Thank you for your suggestion; the essence of the problem is when I nest a switch function, the values calculated through a variable work but not the typed values highlighted in orange. 

       

       

      switch(
          SELECTEDVALUE('Combined Raw Data'[SL ABB]),
          "GBS",
          switch(
          SELECTEDVALUE('Year For Summary'[Year]),
          2019,0.0747,
          2020,0.0408,
          2021, Divide(HighlySatis,TotalResponses),
          2022, Divide(HighlySatis,TotalResponses)
          )