I recently had to come up with a solution for a customer requirement to change the displayed measure as each level of a hierarchy changed by drill down through a column chart. Yikes! But with a little help from the Power BI Community, here’s the solution.
For example, say you have a 4-column table called “Data” with 10 records:
- Column [National] with value of “All”
- Column [State] with values “CO”, “TX” and “AZ”
- Column [City] with the names of cities in CO, TX and AZ
- Column [Population] with a numeric value for population size
On a column chart, I set “Axis” as follows:
And I set “Value” as measure [PopSize], where [PopSize] is like this (pseudocode):
[PopSize] = if [current value of Axis] = “National” then [AllPops] else if [current value of Axis] = ”State” then [AllStatePops] else Sum of [Population]
…where the sub-measures [AllPops] and [AllStatePops] are different aggregations of Data[Population] using different modifications to filter context with CALCULATE.
I define my measures for [AllPops] and [AllStatePops] as follows:
[AllPops] = CALCULATE(SUM(Data[Population]),ALL(Data))
[AllStatePops] = CALCULATE(SUM(Data[Population]),ALL(Data[City]))
The magic happens when you use ISFILTERED() for the “current value of Axis” test inside of an IF() statement, like this:
[PopSize] = IF(ISFILTERED(Data[National]),[AllPops],IF(ISFILTERED(Data[State]),[AllStatePops],CALCULATE(SUM(Data[Population]))))
By using CALCULATE() and ALL() in our sub-measures, the result is that slicers filtering values for Data[State] or Data[City] will not affect the calculation for [AllPops] at the “National” hierarchy level. And slicers that filter Data[City] will not affect the calculation for [AllStatePops] at the “State” hierarchy level.
However, when you drill down to the “City” level of the hierarchy, you will only see the sum of Data[Population] for those cities not filtered by the slicers for State or City, according to the last expression in the IF() statement:
And there you have it. Using a “master measure” like [PopSize], you can use ISFILTERED() within IF() statements to select different “sub-measures” for display at different levels of the hierarchy defined on your Axis.
Now of course this is a contrived example, demonstrating the use of ISFILTERED() to identify the current level in your hierarchy and switch measures. But hopefully after reading this description you can extract the approach and apply it to your own situation.