In the last post I explained how the three types of LOD expressions work. Clearly they make working with multiple levels of aggregation much easier, but we could solve these problems in the past using techniques like table calculations and blending. So how do the two approaches compare?
EXCLUDE expressions create results that are (more aggregated/less granular) than the viz LOD. Previously we often used table calculations to do the same because they are aggregated from the query results. Note that table calculations can only work “up” from the Viz LOD (i.e. more aggregated/less granular).
The addressing fields are the dimensions that would be excluded in the LOD expression.
The equivalent of an EXCLUDE expression could also be achieved by blending. You blend the data source against a copy of itself and explicitly unlink the dimension to be excluded.
The unlinked fields are the dimensions that would be excluded in the LOD calculation.
The equivalent of an EXCLUDE expression can also be achieved with subtotals and reference lines – however these are purely visual constructs and cannot be used in calculations.
To achieve the equivalent of an INCLUDE expression you need to change the Viz LOD and make the query results (less aggregated/more granular).
You start by making the Viz LOD (less aggregated/more granular/lower).
You then use table calculations to aggregate back up to the desired LOD for display. Because there are more Viz LOD records than we need it’s often necessary to filter and keep only one using INDEX()=1 or an equivalent.
Once again, you could also use an unlinked self-blend to roll up from the new Viz LOD to the required display LOD. Same as before, y start by making the Viz LOD (less aggregated/more granular/lower).
The value from the blended data source is then used to show the original Viz LOD. Because there are more Viz LOD records than we need it’s often necessary to filter and keep only one using INDEX()=1 or an equivalent.
What about FIXED expressions, I hear you ask? Well, before LOD expressions creating an aggregation completely independent of the Viz LOD was only possible with custom SQL.