topic Re: Splitting Counties and States and State Code Conversion in DAX Commands and Tips

Splitting Counties and States and State Code Conversion

Anonymous — Wed, 10 Jul 2019 19:22:08 GMT

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?

Re: Splitting Counties and States and State Code Conversion

parry2k — Thu, 11 Jul 2019 05:30:11 GMT

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])

Re: Splitting Counties and States and State Code Conversion

Anonymous — Thu, 11 Jul 2019 15:25:22 GMT

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?

Re: Splitting Counties and States and State Code Conversion

parry2k — Thu, 11 Jul 2019 15:28:13 GMT

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

Re: Splitting Counties and States and State Code Conversion

Anonymous — Thu, 11 Jul 2019 16:52:43 GMT

parry2k

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

Re: Splitting Counties and States and State Code Conversion

parry2k — Thu, 11 Jul 2019 17:06:51 GMT

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.

Re: Splitting Counties and States and State Code Conversion

Anonymous — Thu, 11 Jul 2019 17:39:35 GMT

Perfect! Thank you very much!