Forum Discussion
Sales Territory as a Dimension - DAX Help! (Nested IF statement?)
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
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
4 Replies
vcastelloResolver III
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
Advocate II
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
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
