Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New Column Based on different values

Hi dear Community!

Seeking your help again. 

 

I want to add next column, which case would be for instance: if Column named Branch equals 03 and 04 this should add Spain, and if equals 31, 34, 82,83, 85, 89, 40 then it should bring France, rest Unknown. 

 

I have tried if / if(or (if(and - but it does not work as thoes function only allow a mazimum of 2 arguments, and as you can see I have several. 

 

I have tried Switch as well.

 

I am quite new in Power Bi , so maybe somone can help me write thoes functions or if there are other options do do this task. 

 

Thank you in advance. 

 

 

  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 
    Please select branch number column and go to modeling tab and change the data type of Branchnumber from text to number

    OR

     

    Use value function

    BU = SWITCH(TRUE();
    VALUE(SOMA[Branch Number]) IN {030;042}; "Spain ";
    VALUE(SOMA[Branch Number]) IN {031;034; 082;083;085;402}; "France";
    "UNKNOWN")

    OR in case if you want branchnumber field to be text type only
    BU = SWITCH(TRUE();
    SOMA[Branch Number] IN {"030";"042"}; "Spain ";
    SOMA[Branch Number] IN {"031";"034"; "082";"083";"085";"402"}; "France";
    "UNKNOWN")

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 
    Not sure how exactly your data looks like but you can try this

     

    Column = SWITCH(TRUE()
                    ,'Table'[Branch] IN {3,4},"Spain"
                    ,'Table'[Branch] IN {31, 34, 82,83, 85, 89, 40}, "France"
                    ,"none")

     

    If this does not solve your purpose please share some sample data. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

      I have applied the function, but i get an error: "Table constructor cannot have optional argument"

       

      Bellow I include the full formula in case I am m issing something.

      BU = SWITCH(TRUE();
      SOMA[Branch Number] IN {030;042}; S"pain ";
      SOMA[Branch Number] IN {031;034; 082;083;085;402;}; "France";
      "UNKNOWN")
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous Please place inverted comma at right place
        I guess this is beacuse there is an inverted comma in Spain.

        BU = SWITCH(TRUE();
        SOMA[Branch Number] IN {030;042}; "Spain ";
        SOMA[Branch Number] IN {031;034; 082;083;085;402;}; "France";
        "UNKNOWN")