The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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()
Solved! Go to 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
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
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]
Your code ran successfully but In the colum I am getting blanks
@arjuns0028 post a picture of your code and table where you have column with cities.