Cleaning data calculated field Power Pivot Excel DAX

Question

I have some data of states that looks something like this which starts in cell F8

WI_,
AL_,
MN_,
Minnesota_MN,
UT_,

I want it to look like this

WI,
AL,
MN,
MN,
UT,

I can get it cleaned up with a normal excel formula no problem.

=IF(LEN(F8)>3,RIGHT(F8,2),LEFT(F8,LEN(F8)-1))

I'm trying to implement this into a calculated Field in my Power Pivot. I tried using the following but got an error.

=IF(LEN(Customers[Customer_State])>3,RIGHT(Customers[Customer_State],2),LEFT(Customers[Customer_State],LEN(Customers[Customer_State])-1))

I get the following error:

Calculation error in measure 'Customers'[Clean State]: The value for column 'Customer_State' in table 'Customers' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

Any suggestions on how I can get this to work in a Power Pivot calculated range? Or am I trying to do something that is not possible?

Thanks.

Is it something that is a calculated field rather than a measure? E.g. if the table was in a data model you would add it into a new column as a calculation rather than below the table as a measure? — QHarr
If I understand you correctly it is a calculated field not a measure. — Coopa

Coopa Coopa · Accepted Answer · 2017-12-17T17:34:18

This is the dax code I ended up going with: =IF(LEN([Customer_State])>3,RIGHT([Customer_State],2),LEFT([Customer_State],LEN([Customer_State])-1))

I didnt realized that there are a couple ways to create a calculated field. The method I was using made it kinda confusing. I found this tutorial and it made it a little easier. https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

I know this was kinda confusing, Thanks to those who reached out to help.

Cleaning data calculated field Power Pivot Excel DAX

1 Answers