Forum Discussion

damit23183's avatar
damit23183
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...
Need Help
  • V-lianl-msft's avatar
    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.

amitchandak's avatar
amitchandak
Super User
6 years ago

I do not think this the complete info. But assume table 4 has a country name, you can do like

Score = if(table4[Name] ="USA" , maxx(Table 1,Table 1[Enhance]), if(table4[Name] ="UK",maxx(Table 2,Table 2[Sustain]),
	maxx(Table 2,Table 2[ETE TAT])))

Switch true, you can also use it.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

 

 

  • damit23183's avatar
    damit23183
    Microsoft Employee
    6 years ago
     

    Hi,

    This is orginal table where score has calcualted for Enhance and Sustain which you can see in highlighted below;

     

    Now, after Case statement i would have these result 3576 and 447 in line of highlighted KPI name below;

     

     

     

     

     

     

     

     

     

     

    After applying SWITCH i am not getting anything here, also please one note here there is no relationship between these two tables. 

    Therefore, only with CASE statement i can pull the value here.

     

    Hope this will be enough information.

    Thanks in Advance!

    Thanks

    • V-lianl-msft's avatar
      V-lianl-msft
      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.

      • damit23183's avatar
        damit23183
        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