How to write a formula to translate a number to a text

[dcohare]

Hi,

I have a column called TimeData[Status] that has the following values:

Number	Code
1	In Office
2	Vacation
3	Sick
4	Unpaid Absence
5	Banked Hours Leave
6	Floating Holiday
7	Long Service Leave
8	Purchased Leave
9	Parental Leave
10	Family Leave
11	Progressive Leave
12	Short Term Disabiltiy
13	Bereavement Leave
14	Casual Leave
15	Breastfeeding Leave
16	Special Leave
17	Sick Leave
18	Maternity Leave
19	Jury Leave
21	Study Leave
22	Sundry
23	Matrimonial Leave
24	RnR Leave
25	Bad Weather Leave
26	Relocation Leave
100	Out of Office/No Absence Code

I need to write one formula, similar to an if statement in Excel that basically says "if TimeData[Status]="1","In Office",if(TimeData[Status]="2","Vacation"," etc.

I tried this myself and got the error "DAX comparison operations do not support comparing values of type integer with values of type Text. Consider using the VALUE or FORMAT function." I'm still a basic user so I'm not sure what to do.

Can someone help me write a formula that I can use? Even the start of one that I can finish.

TYIA!

[pranit828 · Accepted solution]

Hi @dcohare 

Try

if TimeData[Status]=1,"In Office",if(TimeData[Status]=2,"Vacation"," etc

Also, Using switch will be a better idea.

Switch(TimeData[Status],

                  1,"In Office",

                  2,"Vacation",
                  ...

                  )

[pranit828 · Accepted solution]

Hi @dcohare 

Try

if TimeData[Status]=1,"In Office",if(TimeData[Status]=2,"Vacation"," etc

Also, Using switch will be a better idea.

Switch(TimeData[Status],

                  1,"In Office",

                  2,"Vacation",
                  ...

                  )

[dcohare · Accepted solution]

Thank you, this worked!

[vanessafvg]

what is your code for your column?

Basically the error message is telling you that the fields you are comparing in your if statement are clashing from a data type perspective.  In order to compare text to text, if you have explicit values of numbers you must ensure the have "" but if you share your code that will make it clear what is going wrong.