Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Fellow PBIers,
Challenge Summary: In short I'm building a world view map of staff locations using bing maps (i.e. "Map" visual icon) WITHOUT latitude and longitude information. The goal is to allow a user to navigate via drill down from Country -> State/ Province -> City and still see indication of a staff member at the City level. However when I include state/province as a location and attempt to drill down say for Argentina (Argentina -> [blank] -> Buenos Aries) it shows results at country level, no results at State / Province level and no results at city level. I want it to ignore a blank state/province where blank and bing should get the city/country combo and then allow the city to show when I go down to city level.
What I've done so far:
thanks in advance and let me know if you need more information.
Solved! Go to Solution.
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]
)
)
See screen shot of issue using very simple setup. The count is simply selecting count for the employee ID field. It's NOT a measure.
Hi @bwarner87
Thanks for reaching out to us.
>> However when I include state/province as a location and attempt to drill down say for Argentina (Argentina -> [blank] -> Buenos Aries) it shows results at country level,
so it should show results at city level when you drill down from State/ Province -> City, right?
could you share a sample file and the right results when visual is at city level? so that we can troubleshoot the measure code. Thanks.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |