Grouping

[arjuns0028]

How to create these groups 

Bangalore - Bangalore

Mumbai - Dombivali, Kalyan, Mumbai, Navi Mumbai, Panvel, Thane, Vasai

Delhi - Faridabad, Ghaziabad, Greater Noida, Gurgaon, New Delhi, Noida

Kolkata - Kolkata, Howrah

Pune - Pune

I also tried to apply a DAX formula

City_Category = IF (     [City_Name] = "Dombivali" ||     [City_Name] = "Kalyan" ||     [City_Name] = "Mumbai" ||     [City_Name] = "Navi Mumbai" ||     [City_Name] = "Panvel" ||     [City_Name] = "Thane" ||     [City_Name] = "Vasai",     "Mumbai",     IF (         [City_Name] = "Bangalore",         "Bangalore",         IF (             [City_Name] = "Faridabad" ||             [City_Name] = "Ghaziabad" ||             [City_Name] = "Greater Noida" ||             [City_Name] = "Gurgaon" ||             [City_Name] = "New Delhi" ||             [City_Name] = "Noida" ||             [City_Name] = "Delhi",             "Delhi",             IF (                 [City_Name] = "Kolkata",                 "Howrah",                 IF (                     [City_Name] = "Pune",                     "Pune",                     BLANK()

[Anonymous · Accepted solution]

Hi @arjuns0028 ,

1. Custom column(m):

if List.Contains({"Bangalore"},[City_Name]) then "Bangalore" else if List.Contains({"Dombivali","Kalyan","Mumbai","Navi Mumbai","Panvel","Thane","Vasai"},[City_Name]) then "Mumbai" else if List.Contains({"Faridabad","Ghaziabad","Greater Noida","Gurgaon","New Delhi","Noida"},[City_Name]) then "Delhi" else if List.Contains({"Kolkata","Howrah"},[City_Name]) then "Kolkata" else if List.Contains({"Pune"},[City_Name]) then "Pune" else null

2. Calculated column(dax):

City_Category2 = 
SWITCH(
    TRUE(),
    'Table'[City_Name] = "Bangalore","Bangalore",
    'Table'[City_Name] IN {"Dombivali","Kalyan","Mumbai","Navi Mumbai","Panvel","Thane","Vasai"},"Mumbai",
    'Table'[City_Name] IN {"Faridabad","Ghaziabad","Greater Noida","Gurgaon","New Delhi","Noida"},"Delhi",
    'Table'[City_Name] IN {"Kolkata","Howrah"},"Kolkata",
    'Table'[City_Name] = "Pune","Pune"
)

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[dufoq3]

Hi @arjuns0028, if you want to write it as measure in DAX, it should be something like this:

City Category = 
SWITCH(
    TRUE,
    SELECTEDVALUE('Grouping'[City Name]) IN {"Dombivali", "Kalyan", "Mubai"}, "Mumbai",
    SELECTEDVALUE('Grouping'[City Name]) IN {"Kolkata", "Howrah"}, "Kolkata",
    "-"
)

 

But there are many ways. You can also create separate table with [City Name] and [City Category] combinations and connect this new table to your data table via [City Name]

[arjuns0028]

Your code ran successfully but In the colum I am getting blanks 

[dufoq3]

@arjuns0028 post a picture of your code and table where you have column with cities.