New Column Based on different values

[Anonymous]

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]

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]

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]

@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")

[Anonymous]

@AnonymousStill same. Please see a screeshot. 

[Anonymous]

@Anonymous  Please remove the semicolon after 402 value

BU = SWITCH(TRUE();

SOMA[Branch Number] IN {030;042}; "Spain ";

SOMA[Branch Number] IN {031;034; 082;083;085;402}; "France";

"UNKNOWN")

[Anonymous]

@AnonymousVery True!

Corrected but now I am getting different error ( and I have changed format- the Branch Number column is under text format). 

[Anonymous · Accepted solution]

@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")

[Anonymous]

BINGO!! 🙂 

Thanks a lot. I opted fot the last option. 

Coffe from me 🍮