Forum Discussion
Helper IIRegions based on States
Good Morning Community and Happy Friday 🙂
I have a column namely, "States". Is there a formula or a way in PowerBI where I can know the region based on state
e.g. CA i.e. California will be West. Now, I've seen a lot of fellow peers plug in the formula manually, was wondering if there was a better way.
StatesColumn
reportuser yes if you set the relationship between you old and new table, you can add a column in your old table like this
Region = RELATED ( NewTable[Region] )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
7 Replies
- parry2k
Super User
reportuser not direct way until you find a wiki source somewhere online that has divided states in the region and you can connect to that web table and use in your model.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
reportuserparry2kbased on your response I found an excel sheet with States and Regions. Advice on how to connect them to the existing table.
- parry2k
reportuser there are few ways, you can connect to this excel sheet and use merge option in power query between your existing table and this new table on state column and then extract region from new table, or load new excel sheet in the model and set relationship in the data model between your existing table and this new table on the common field (which I guess will be a state)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- negi007
Community Champion
reportuser There is a workaround for your problem. I suggest you to use the shape map of the country. Then in data colors, you can colors states by a color. You can have one color for all states from one region and same way for other region. This will help surely resolve the issue without doing any other additon in the data.
