Nested Switch Function

osuraperera86 — Tue, 22 Nov 2022 09:08:44 GMT

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)

)

Re: Nested Switch Function

daXtreme — Tue, 22 Nov 2022 09:32:21 GMT

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.

Re: Nested Switch Function

osuraperera86 — Tue, 22 Nov 2022 09:39:22 GMT

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)

)

topic Re: Nested Switch Function in DAX Commands and Tips

Nested Switch Function

Re: Nested Switch Function

Re: Nested Switch Function