Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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
Not applicable

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
Not applicable

@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
Not applicable

 

@AnonymousStill same. Please see a screeshot. 

 

c.PNG

Anonymous
Not applicable

@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
Not applicable

@AnonymousVery True!

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

 

c.PNG

Anonymous
Not applicable

@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
Not applicable

BINGO!! 🙂 

Thanks a lot. I opted fot the last option. 

 

Coffe from me 🍮

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors