Forum Discussion

Microsoft Employee

6 years ago

Solved

SWITCH / CASE statement not working

So here is detail, Table 1: Enhance -- Column Name 4773 Table 2: Sustain -- Column name 446 Table3: ETE TAT -- Column Name 1 Now, Current Output table is like below; (As you...

V-lianl-msft
6 years ago
Hi damit23183 ,

First you need to create two measures to calculate the sum of "enhance" and "sustain",
sum_enhance = CALCULATE(
SUM(SERVICE[NO. of ADO by Enhance]),ALL(SERVICE))

sum_sustain = CALCULATE(
SUM(SERVICE[No. of ADO by Sustain]),
ALL(SERVICE))

then use the "switch" function to create a new measure and drag it into the table
Score_Measure = SWITCH(MAX(Metric[Metric Name]),"A",MAX(Metric[Score]),"B",[sum_enhance],"C",[sum_sustain],"D",MAX(Metric[Score]))

You can refer to the pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

6 years ago

Hi damit23183 ,

First you need to create two measures to calculate the sum of "enhance" and "sustain",

sum_enhance = CALCULATE(

SUM(SERVICE[NO. of ADO by Enhance]),ALL(SERVICE))

sum_sustain = CALCULATE(

SUM(SERVICE[No. of ADO by Sustain]),

ALL(SERVICE))

then use the "switch" function to create a new measure and drag it into the table

Score_Measure = SWITCH(MAX(Metric[Metric Name]),"A",MAX(Metric[Score]),"B",[sum_enhance],"C",[sum_sustain],"D",MAX(Metric[Score]))

You can refer to the pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Microsoft Employee

6 years ago

Hi,

THank you for your response and it worked great.

However, have another question on how to find count, sum, average by category.

I.e Count (ID) by Service only. Just like doint Count by Group in SQL.

i would like to do Average, Sum and Count by Category in PowerBI Desktop.

Thanks

Related content