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

Convert number field to a new description field

I'm looking for a way to create a new description field that translates a number to a name.  For example, I want the value 1 (from Type field) to equal "Major" in a new (Description field) and 2 to equal "Minor" and so on.  I believe DAX might get me there, but need a little assistance to get started.  Thank you!

1 ACCEPTED SOLUTION

Hi @Anonymous  The format of the SWITCH statement follows a pattern.

 

It goes, <test> and is then followed by pairs of IF/THEN which you can have as many as you like

 

but finally you need an <else> so please try this:

 

 

Column = SWITCH(Table[TypeID] ,

1 , "Major" ,

2 , "Minor" , "other" )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Phil_Seamark
Employee
Employee

Hi @Anonymous

 

Sounds straight forward.  If your table was called Table1 and the numeric column was called Status you could add a calcuated column to your table like this:

 

New Column = SWITCH('Table1'[Status] , 
1 , "Major" , 
2 , "Minor" , 
3 , "and so on" , 
"Other" 
)

Give it a go and let us know how you get on


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

@Phil_Seamark Thanks for the tip!  When I put that in, I get "Argument '6' in SWITCH function is required"

 

I put the following in:

 

Column = SWITCH(Table[TypeID] ,

1 , "Major" ,

2 , "Minor" , )

 

What am I missing here?  Thanks!

Hi @Anonymous  The format of the SWITCH statement follows a pattern.

 

It goes, <test> and is then followed by pairs of IF/THEN which you can have as many as you like

 

but finally you need an <else> so please try this:

 

 

Column = SWITCH(Table[TypeID] ,

1 , "Major" ,

2 , "Minor" , "other" )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

@Phil_Seamark - Oddly enough, when I installed the March 2017 build of Power BI, it seems to have broken this statement!  Now, it says that "A single value for column 'type' in table 'data' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.  Thoughts?

Hi @Anonymous,

 

I just tried it in the March version and it seems to be working.  Are you definitely adding the code to a Calculated Column rather than a Measure?

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

You nailed it!  Sorry, I didn't realize they weren't interchangeable I guess.  Thanks for the quick response!

Anonymous
Not applicable

That did it!  Really appreciate the help!

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.