Using IF function with DAX to classify account types

[Anonymous]

Hello all! I'm fairly new to using DAX and Power BI and I keep getting stumped using the IF function. 

I'm trying to create a new column in 'edit queries' where I'm trying to classify account codes by account type. Specifically, I have a column of Account Codes (7010, 7209, 7997, 8010, 8116, ect) and I want all the accounts beginning with 7 to be classified as "Personnel," and all the accounts beginning with 8 to be classified as "General". (All the account codes begin with 7 or 😎 

I assumed that I would be using an IF function, however, I'm not sure what the correct formatting is and I keep getting error messages particularly this one: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

I've tried many different functions. This is the latest one I've tried:

Column = IF(DimAccount[AccountCode]<=7999,"Personnel","General")

Any feedback would be much appreciated!

[Vvelarde · Accepted solution]

Hi @Anonymous make sure thar Account Codes columns is not a text field.

[Vvelarde · Accepted solution]

Hi @Anonymous make sure thar Account Codes columns is not a text field.

[jahida · Accepted solution]

@Vvelarde is almost certainly right; the workaround is just to change your formula to:

Column = IF(VALUE(DimAccount[AccountCode])<=7999,"Personnel","General")

However, I would say better practice might be:

Column = IF(LEFT(DimAccount[AccountCode]) = "7", "Personnel", "General")

just because it makes it a bit more clear to me. Your call.

[Anonymous]

@Vvelarde You were definitely right! It was as simple as changing Account Codes to a number field. 

@jahida I went for your advice and incorporated the LEFT function for best practice. 

Thank you so much for your advice! Much appreciated.