Forum Discussion
Using IF function with DAX to classify account types
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 8)
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!
Hi Anonymous make sure thar Account Codes columns is not a text field.
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.
3 Replies
Hi Anonymous make sure thar Account Codes columns is not a text field.
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.