Sync visuals with Drill-down
Power BI has a great functionality of Drill-down. Once you add a hierarchy to the visual, then you can let end user decide how he would like to browse through the items. It is handy especially on maps where typical hierarchy is from continents, thru countries to cities. If we take a look at map we wouldn’t want to see all the cities in the world at the same view. It would be pretty unreadable. And this is the exact scenario where, hierarchy drill-down helps a lot.
However, there is a small pitfall when it comes to hierarchies. Microsoft has not created an easy way to sync two visuals to represent the same level of hierarchy at the same time. It’s not that uncommon that I would like to show same items on axis of a chart as current level of detail on the map. Of course there are workarounds like drill-through option, but they are not as smooth in action as solution I came with.
The trick I came with is actually a modification of BI Elite solution for dynamic legend/axis that can be found here: https://www.youtube.com/watch?v=8e8a3o1w51M
Below you will find my video that takes this solution to the next level:
So the trick is based on an extra table with 3 columns:
- Name column – with all possible values of every hierarchy
- Field column – with values that correspond to level of hierarchy
- Key column – column with unique ID to make inactive relationships
DAX code of my table below:
Geo = UNION ( SELECTCOLUMNS ( continents, "Key", continents[continentKey], "Name", continents[continent], "Field", "Continent" ), SELECTCOLUMNS ( countries, "Key", countries[countryKey] + 1e3, "Name", countries[country], "Field", "Country" ), SELECTCOLUMNS ( cities, "Key", cities[cityKey] + 1e6, "Name", cities[city], "Field", "City" ) )
Then you need to play a bit in Model view where you need to set up inactive relationships. Make sure it is connected with one-to-many relationships (where one is on our new table side). Example below.
Those relationships are inactive so they do’not make any change till you activate them with some DAX. How to do that? You need to utilize USERELATIONSHIP formula. Below there is a code I used in my example.
Users geo = SWITCH (
TRUE(),
CALCULATE(HASONEVALUE(countries[countryKey]),ALLSELECTED(countries)),
CALCULATE (
[Users],
USERELATIONSHIP ( Geo[Key], cities[cityKey_temp] )
),
CALCULATE(HASONEVALUE(continents[continent]), ALLSELECTED(continents)) || COUNTROWS(countries)<=20,
CALCULATE (
[Users],
USERELATIONSHIP ( Geo[Key], countries[cityKey_temp] )
),
CALCULATE (
[Users],
USERELATIONSHIP ( Geo[Key], continents[continentKey])
)
)
Below there is a sample report where I used similar technique on “Geography” page