Forum Discussion
Bing Map - World view with a Location Hierarchy including State/Province Option
- 4 years ago
I found a solution that works for me. After re-reading THIS, I decided to create a calculated column titled Place with a conditional statement to send different combinations (3digit countr code OR city, 3 digit country code, OR city, state/province, 3 digit country code) to location services. I also gave the calculated column the "Place" data category designation.
I then use Country and Place (new calculated column) as the locations. When i'm at the Place hierarchy level, It now more accurately maps the staff that are in countries with state province to the right location (i.e. Hollywood, Florida, USA VS Hollywood, California, USA) and let's me see staff in cities without a state/province. (i.e. Rome, Italy)
Hope this works for others!
Here's my rough DAX which could probably be made more efficient with a SWITCH function.
Place = IF(ISBLANK('Master Employee Data'[City]) || 'Master Employee Data'[City] = "", 'Master Employee Data'[Country], IF(ISBLANK('Master Employee Data'[State/Province]) || 'Master Employee Data'[State/Province] = "", 'Master Employee Data'[City] & ", " & 'Master Employee Data'[Country], 'Master Employee Data'[City] & ", " & 'Master Employee Data'[State/Province] & ", " & 'Master Employee Data'[Country] ) )
I found a solution that works for me. After re-reading THIS, I decided to create a calculated column titled Place with a conditional statement to send different combinations (3digit countr code OR city, 3 digit country code, OR city, state/province, 3 digit country code) to location services. I also gave the calculated column the "Place" data category designation.
I then use Country and Place (new calculated column) as the locations. When i'm at the Place hierarchy level, It now more accurately maps the staff that are in countries with state province to the right location (i.e. Hollywood, Florida, USA VS Hollywood, California, USA) and let's me see staff in cities without a state/province. (i.e. Rome, Italy)
Hope this works for others!
Here's my rough DAX which could probably be made more efficient with a SWITCH function.
Place =
IF(ISBLANK('Master Employee Data'[City]) || 'Master Employee Data'[City] = "",
'Master Employee Data'[Country],
IF(ISBLANK('Master Employee Data'[State/Province]) || 'Master Employee Data'[State/Province] = "",
'Master Employee Data'[City] & ", " & 'Master Employee Data'[Country],
'Master Employee Data'[City] & ", " & 'Master Employee Data'[State/Province] & ", " & 'Master Employee Data'[Country]
)
)