Forum Discussion

damit23183's avatar
damit23183
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Need help with SWITCH funtion

Hi, 

I need some help in creating SWITCH funtion which is nothing but CASE statement in POWERBI.

SWITCH funtion has used to calculate final output based on conditions and values will be come from different tables.

 

Source:

Name (Table0) 

USA, UK , Canada 

 

Values1: 

Table 1: 

Score1Measure: 4000 for USA

 

Values2: 

Table 2: 

Score2Measure: 1 for UK

 

Values3: 

Table 3: 

Score3Measure: 0 for CANADA

 

After applying Switch funtion like below;

 

SWITCH(TRUE(),'Table'[Name]="USA",[Score1Measure], 
'Table'[Name]="UK",[Score2Measure], 
'Table'[Name]= "Canada",[Score3Measure],
 0)

it gave me wrong output as shown in table below. USA should have 3000. It does look like automatically average out but i checked data type and summary which is SUM. Not sure what could be wrong here. Any advise would be appreciated!

 

OUTPUT :

 Table: 

 

Name                              Score

USA                                  3000

UK                                         1

Canada                                  0

 

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    The switch across the table needs aggregated values from other tables. Can you share some better sample data and sample output?

    • damit23183's avatar
      damit23183
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      There are total 3 Tables in data PowerBI data model;

      1) Fact_Incident

      2) Delivery 

      3) Customer Experience

       

      These are the following data and columns in each table

      Fact_Incident Table:

      TAT Time --> (Column Name)

      1

       

      Delivery Table:

      No of Enhance                No. of sustain      -->(Column Name)

      3000                                     445

       

      Custom Table: (This is out put table where Score is empty)

      Name              Score    -->(Column Name)

      USA

      UK

      Canada

       

      After SWITCH or any CASE function i would like to see table as below;

       

      Custom Table: (Fianl Output)

      Name              Score    -->(Column Name)

      Enhance             3000

      Sustain                445

      TAT                        1

       

      Hope this will give you more detail.

       

      NOTE: There is no relationship between source table and output table this is just case statement i would like to use here and put whatever value is there in source.

       

      Thanks