Forum Discussion
State Numbers Converted to State Names
I am trying to create a column called 'State' that shows the state that a policy belongs to.
The column that I am using to populate 'State' is called 'StateIDsAll'. The 'StateIDsAll' has numerous numbers in it that correspond to states as seen below (1 = Alabama, 2 = Alaska, 3 = Arizona, etc)
The highlighted selection above has '1, 4' in it, so when the 'State' column is created, '1, 4' should read 'Alabama, Arkansas'
I have created a new table that has columns called 'State Number' and 'State Name', but I am not sure where to go after that.
Hi i820017
From this point, you need to create a relationship between the tables that you ate at.
In order to assist in a more informed manner
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
5 Replies
Hi i820017
From this point, you need to create a relationship between the tables that you ate at.
In order to assist in a more informed manner
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Here is a Dropbox link to the PBIX file that shows what I am looking for.
It has 2 fields:
- State_Names_and_Numbers
- This field is the one that I am going to use in my Power BI Report
- State_Numbers_and_their_State_Names
- This field is an example of what I am looking for
The page entitled 'Completed Questions' has the field 'State_Numbers_and_their_State_Names' in it
Right now, the data that is getting loaded into the report that I am building has a number assigned to a state.
1 = Alabama, 2 = Alaska, 3 = Arizona, etc
The issue is that multiple numbers (and therefore multiple states) can show up in a row.
For example, in the second row of the 'Completed Questions' page, there are state numbers '1,2' which should show 'Alabama, Alaska' in the completed report.
I am trying to create a DAX column that will create the desired column called 'State Names'
Based on the file, I couldn't figure out what you were trying to accomplish.
Are all possible combinations included in the data you are analyzing?When a user chooses a country, are they choosing a number or a word?
Why does he choose combinations over countries, rather than a country followed by a combination?
Here are the answers to your questions
Are all possible combinations included in the data you are analyzing?
No, all possible combinations are not included in the data that I am analyzing. I would use a SWITCH function if that were the case
When a user chooses a country, are they choosing a number or a word?
When a user chooses a state (not a country...I apologize for being unclear on that), then he is choosing a number. 1 = Alabama, 2 = Alaska, 3 = Arizona, etc
Why does he choose combinations over countries, rather than a country followed by a combination?
He is choosing states, not countries...He is assigning policies to certain states. For example, policy A might be assigned to 1 state or more than 1 state. Policy B might be assigned to 1 state or more than 1 state, etc
- State_Names_and_Numbers
Sorry for being unclear...I am trying to get a column that fills in the correct state for a state number. The state numbers column has already been entered. It comes from a Sharepoint site. It is titled 'StateIDsAll' in the link below.
https://www.dropbox.com/s/hdnr6n8as02p3ev/Screenshot%202023-06-22%20130445.png?dl=0
The highlighted part should read 'Alabama, Arkansas'
