Displaying drill-down dependent targets: visual calculations to the rescue!

Recently, I turned to my LinkedIn network for help. I was stuck on a specific DAX/visualization challenge. The solution eventually came through LinkedIn, and in this post, I’ll walk you through the problem, the failed attempts, and the winning approach using Visual Calculations (VCs).

‍

The challenge

A client asked me to show the average number of actions per employee on different time levels: hour, day, week, and month. Each level has its own performance thresholds: "improvement needed", "good", and "excellent".

Targets for hour and day were predefined. Week and month targets I derived by multiplying the day target by the number of working days (ignoring holidays for simplicity).

‍

The visualization: line chart with drill-down

In a line chart with drill-down, I displayed the actions per employee across time. I used ISINSCOPE() to determine the current drill level and show the right target line per category:

- Highest threshold for improvement needed

- Lowest threshold for excellent

- The rest = good

These lines were added but styled invisibly to support bandwidth coloring instead.\

Target actions per MW_excellent=
	SWITCH(TRUE(),
		ISINSCOPE('hourtable'[Hour of action]), 16,
		ISINSCOPE('General Dim_dates'[shortday], 91,
        ISINSCOPE('General Dim_dates'[week], [trgt week excellent],
        ISINSCOPE('General Dim_dates'[Monthshort], [trgt Month Excellent]
)

Line chart with actuals and target lines.

Colouring bandwidths

I added three coloured bands:

Light orange: below "improvement needed"
Light yellow: between "improvement needed" and "excellent"
Light green: everything above "excellent"

The first two were easy with error bars and target measures. But defining the upper limit for 'excellent' turned out to be the tricky part...

(btw: initially,there was a small gap between “improvement needed” and “good”. Later we decided to make them meet at “improvement needed”. Sorry for any inconsistencies in this article and accompanying visuals)

The problem: defining the upper limit for ‘excellent’

The potential number of actions that would fall in de excellent bandwidth was huge, making it hard to determine the heigth of the bandwidth. Hardcoding a max value (e.g. 100 actions/day) fails when performance goes beyond that. We needed the upper limit to follow the data dynamically.

I built a DAX measure using ALLSELECTED() and MAXX() per drill level. It worked… until higher drill levels showed jagged, unstable bands.

EB_Excellent_Upper = 
VAR MaxPerHour =
    MAXX (
        ALLSELECTED('trustit Fact_ExecutedActions'[Uur van actie]), 
        [Aantal acties per MW]
    )
VAR MaxPerDay =
    MAXX (
        ALLSELECTED ('General Dim_Dates'[ShortDay]),
        [Aantal acties per MW]
    )
VAR MaxPerWeek =
    MAXX (
        ALLSELECTED ('General Dim_Dates'[Week van jaar]),
        [Aantal acties per MW]
    )
VAR MaxPerMonth =
    MAXX (
        ALLSELECTED ('General Dim_Dates'[MonthShort]),
        [Aantal acties per MW]
    )

-- Upper limits
VAR _UpperHour = 
CALCULATE(MaxPerHour * 1.2, ALLSELECTED('trustit Fact_ExecutedActions'[Uur van actie]))

VAR _UpperDay = 
CALCULATE(MaxPerDay * 1.2, ALLSELECTED('General Dim_Dates')) 

VAR _UpperWeek = 
CALCULATE(MaxPerWeek * 1.2, ALLSELECTED('General Dim_Dates'))

VAR _UpperMonth = 
CALCULATE(MaxPerMonth * 1.2, ALLSELECTED('General Dim_Dates'))

VAR _result =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'trustit Fact_ExecutedActions'[Uur van actie] ),
            IF ( MaxPerHour < 16, 18, _UpperHour ),
        ISINSCOPE ( 'General Dim_Dates'[ShortDay] ),
            IF ( MaxPerDay < 91, 100, _UpperDay ),
        ISINSCOPE ( 'General Dim_Dates'[Week van jaar] ),
            IF ( MaxPerWeek < [target acties per MW_Excellent], 500, _UpperWeek ),
        ISINSCOPE ( 'General Dim_Dates'[MonthShort] ),
            IF ( MaxPerMonth < [target acties per MW_Excellent], 2100, _UpperMonth)
    )

RETURN
_result

When values stay within the excellent thresholds, the upper limit line looks nice and flat. But when values exceeded thresholds, the graph broke down. I was stuck — and posted my challenge on LinkedIn...

Upper band works fantastic as long as values aren't high.

Upper band becomes a mountain landscape with higher actual values.

A fresh look: enter visual calculations

Several suggestions came in. Most I had tried already. But then Erik Svensen reached out. We’d met at conferences before, and he’s great with Visual Calculations. He proposed a new approach using ISATLEVEL(), the VC version of ISINSCOPE(). I sent him a dataset,and we collaborated on a clean solution.

The solution: a clean and dynamic visual calculation

We simplified everything. Only one Visual Calculation was truly needed: a dynamic upper limit for the excellent range. It checks the current drill level and decides:

If values stay below the threshold → return fixed max
If values exceed it → return actual max * 1.1 (headroom)

Upper limit Excellent = 

  SWITCH(
    TRUE(),
    ISATLEVEL([Uur van actie]), 
        IF(    
        MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [Uur van actie]) )< 16,20,
        MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [Uur van actie]))),

    ISATLEVEL([ShortDay]), 
        IF(
        MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [ShortDay] )) < 91, 100,
        MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [ShortDay] ))),
        
    ISATLEVEL([Week van jaar]),  
        IF(
        MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [week van jaar] ))< 5*91, 520,
        MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [week van jaar] ))),

    ISATLEVEL([MonthShort]),  
        IF(
        MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [MonthShort] )) < [Trgt Maand Excellent], 91*5*5,
        MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [MonthShort] ))),
             
    ISATLEVEL([Year]), MAXX(ROWS, EXPAND(MAX([Aantal acties per MW]), [Year] ))
  ) * 1.1

ISATLEVEL() vs. ISINSCOPE()?

ISINSCOPE() checks the current hierarchy level in classic DAX.
ISATLEVEL() does the same — but inside visuals, for VC logic.

ROWS() + EXPAND() = ALLSELECTED()?

Yes. Kind of.

In VC, this combo behaves like ALLSELECTED(), but only considers what's visible in the visual, not slicers or pagefilters.

The result

A dynamic visual that responds to:

Drill-down level
Real data values, also if impressively high

‍

‍

Enhancements: labelling the categories

To make the visual even more informative, we added three extra VCs to label each category band in the center. Roughly calculated as:

(Upper limit - Lower limit) / 2 + Lower limit

Line chart with dynamic area bands and labelling of the categories.

‍

And this is what the build panel looks like. The VCs (on the y-axis) are marked with a small bar chart icon and "fx".

‍

Closing thoughts

Huge thanks to Erik Svensen for his help! Without him, I’d still be fighting jagged graphs and clunky DAX. As promised, I’ve started digging into Visual Calculations.

And yes… Jeroen (Jay) ter Heerdt, the mind behind VCs at Microsoft: you were right. They’re not just powerful — they’re essential.

I’ve completed the Microsoft Learn module, but that felt like just scratching the surface.

👉 What do you think are the best resources to truly learn Visual Calculations?
‍

Preferably online, and ideally with real-world examples. Drop me a link, blog, or training tip… I’d love to learn more!

‍