Sales Territory as a Dimension - DAX Help! (Nested IF statement?)

[Anonymous]

My company operates across the U.S.  I would like to align cities with a sales territory so that I can see performance dimensiolanized a different way.

In my dataset I have the cities listed, but am looking for instruction how I can create a dimesion such as Northeast, Southeast, Midwest, etc.

I am looking for some instructions for a DAX statement (If statement?) that will allow me to assign all of my Northeast cities to a Northeast classification, and so on.  

i.e. New York, Philadelphia, Baltimore, Pittsburgh = Northeast

i.e. Raleigh, Charlotte, Atlanta, Orlando = Southeast

[vcastello · Accepted solution]

Hi @Anonymous

Why don't you just ...

1.- Create a table in Excel with the list if cities and their classification.

2.- Import through the Query Editor that table to Power BI.

3.- Create a relationship between the cities table you just created and the one you have the data in.
4.- Apply the visual you need.

Hope That Helps

Vicente

[sumit4732]

Hi @Anonymous,

You can enter mapping table for cities to territory and then use lookupvalue function in your existing dataset to get territory as a custom column.

Hope this helps.

Let me know if you have any questions on this.

-Sumit

[Anonymous]

@sumit4732

Sumit, thanks for the speedy advice.

I am afraid I would need a little more detail, because I am very much a newbie.

My preference would be to use a statement in a column if that was possible?  The only If statements I have used in Power BI, have been binary and have not included this many values.

Please let me know your thoughts.  Thanks!

[sumit4732]

Hi @Anonymous,

Firstly, I don't think IF will be efficient here as it only checks one condition, you might have to use SWITCH here.
Secondly, if you have few cities using a switch is fine but when you have a lot of cities I will suggest using a table in place of that and use LOOKUPVALUE function.

Hope this helps.

-Sumit