State Numbers Converted to State Names

[i820017]

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.

[i820017]

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'

[i820017]

Here is a Dropbox link to the PBIX file that shows what I am looking for. 

https://www.dropbox.com/s/m2idqe7zakvwi3n/BackOffice%20QQ%20Users%20Working%20for%20Power%20BI%20Hel...

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'

[Ritaf1983]

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?

[i820017]

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

[Ritaf1983 · Accepted solution]

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