Splitting Counties and States and State Code Conversion

[Anonymous]

I have a column called State/Country and it contains either a country name (e.g. Korea) or a state code (e.g. CA)

What I need to do is to separate those into 2 columns:

Var = State/Country

If length(Var) > 2 then CountryColumn = Var, StateColumn = “ “

Else CountryColumn=USA, StateColumn = decodeStateAbbr (??)

My questions: how can I write this in DAX and how can I decode state abbriviation?

[parry2k · Accepted solution]

@Anonymous not sure wht you mean by decode state abbreviation. here is calculated columns looks like

Country = IF( LEN(Table[Country/State]) > 2, Table[Country/State], "USA" )

State = IF( LEN(Table[Country/State]) > 2, "", Table[Country/State])

[Anonymous]

@parry2k 

Thank you! By decoding I mean converting CA to California, NY to New York, etc. May be I should split the column first and then decode the State?

[Anonymous]

@parry2k 

Is there a function to do that conversion or how is it done?

[parry2k]

@Anonymous you can add another table in your model with 2 digit state code and full name and then set relatonship with that table to get the full name. Populating that table must be super easy or you can connect to wikipedia using power query to get list of states and abbreviations. 

[Anonymous]

Perfect! Thank you very much!

[parry2k]

@Anonymous sure I already did the split for you and from there you can decode to long state name.