In my spare time, I’m working on a viz on global immunization rates for 1 year olds from the WHO. The dataset has 2 levels of geography – country and region.
This is quite simple to do in a “top-down” scenario (for instance, region to country), but how do we get this to work the other way around?
What if I wanted to select a country and see what other countries are in the same region? Specifically, for the map to zoom in on its respective region?
First off, there are a couple of clues we can pick up from this problem:
1) We can’t use a filter on country, because it will obviously filter to the selected country.
Rather we need to filter to 1 level above this, region. In my mind, I’m already sensing we’ll have to do an “if” statement of sorts.
2) Since we cannot create a filter and will have to use the if statement, we’ll be using a parameter.
Big thank you to Craig Bloodworth for helping me work through this problem 🙂Â
How do we get this action to work?
Step 1: Create a parameter that determines which region was selected.
How to create a parameter:
Step 2: Create the calculation which drives the parameter:
Here’s a close up view of the calculation:
In this instance, when we select a country in the parameter, the IF statement either returns Null or the Region. We can see that working in this “sanity check” table.
You might be wondering why we need the MAX() function. Indeed, if we were to take out the max function we would get the same result in the table. Why even use it to begin with?
The reason why we cannot directly use a calculation where we specify the country, but ask it to return its respective Region is because it will only bring back data on the Region related to it, almost thinking it is the only stance of this Region. i.e. If we only select “Canada”, it will only retrieve regions where country=”Canada”. Since this is at a lower level of granularity, it will only bring back 1 stance of “America” within Region, instead of all instances of “America”.
Therefore, we need to create a scenario where the view is supplemented with extra context, achieved through a FIXED level of detail calculation.
However, Level of Detail calculations can only be done on aggregations and fundamentally, it has to be a measure. That means in order for us to access the ability to use a LOD calculation, we need to turn our parameter control function into an aggregation – our MAX() function. We will build the LOD calc in the next step.
This is the first time I’ve used a traditional dimension and processed it as a measure and aggregation. Totally turned my head upside down!
Step 3: Creating the filter calculation for 1 level above the detail in the view
Our first instinct here might be to create this calculation.
But of course it doesn’t work because our selected region is still an aggregation thanks to our MAX() function. We can remove the aggregation with the { } brackets, creating a FIXED LOD calculation. Remember it has to be fixed in order for the the data to go past the filter. Yay to the order of operations!
Correct calculation
Bring the calculation to filter and voila! The functionality will start working in the table.
It looks even better with a map 🙂
Happy Vizzing!