Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated column using IF and OR statements (or SWITCH?)

Hello,

I'm trying to create a calculated column based on an existing column and using IF and OR statements to change 3 values. It doesn't seem to work if I have different if values. I even tried SWITCH but it doesn't seem to work with a column as the <else> value.

 

FA = SWITCH(TRUE(), 'ProgramIntermediate'[Project ID]="50323" && 'ProgramIntermediate'[Project ID]="50279","Project A", 'ProgramIntermediate'[Project ID]="12347" && 'ProgramIntermediate'[Project ID]="12347","Affiliate" -'ProgramIntermediate'[Focus Area])
 
'ProgramIntermediate'[Focus Area] is the existing column I want to replicate
I want to change the values of the column to "Project A" if the ID is 50323 and 50279 and change the value to "Affiliate" if the ID is 12347 and 12347.
 
Any ideas? Thanks!
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    Not sure if I understand it correctly, if Project ID = 50323 or 50279 then Project A else if Project ID = 12347 then Affiliate else Focus Area? As you have 12347 twice, so just put one...

     

    FA =
SWITCH (
    TRUE (),
    'ProgramIntermediate'[Project ID] = "50323"
        || 'ProgramIntermediate'[Project ID] = "50279", "Project A",
    'ProgramIntermediate'[Project ID] = "12347", "Affiliate",
    'ProgramIntermediate'[Focus Area]
)

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    Not sure if I understand it correctly, if Project ID = 50323 or 50279 then Project A else if Project ID = 12347 then Affiliate else Focus Area? As you have 12347 twice, so just put one...

     

    FA =
SWITCH (
    TRUE (),
    'ProgramIntermediate'[Project ID] = "50323"
        || 'ProgramIntermediate'[Project ID] = "50279", "Project A",
    'ProgramIntermediate'[Project ID] = "12347", "Affiliate",
    'ProgramIntermediate'[Focus Area]
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Sorry, Anonymous  I made a typo. It's actually 4 values:

      FA = SWITCH(TRUE(), 'ProgramIntermediate'[Project ID]="50323" && 'ProgramIntermediate'[Project ID]="50279","Project A", 'ProgramIntermediate'[Project ID]="50316" && 'ProgramIntermediate'[Project ID]="50381","Affiliate",'ProgramIntermediate'[Focus Area])
       
      Yes, the logic is if Project ID is 50323 and 50279, then Project A; if Project ID is 50316 and 50381, then Affiliate, else Focus Area. I tried the above and it didn't do anything. It's no longer an error, but it just replicated all the Focus Area values and nothing was changed.
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Oh wait,  i didn't change the && to II. It worked! Thank you!!!