Hi everyone,
I have a scatter chart with a hierarchy as values (let's say Country/Region/City).
I want to add a constant line on this chart based on the level of hierarchy the user is seeing. For example is the user is on the Country level the constant line would be the result of calculation A, on the Region level it would be the result of calculation B and on the City level it would be the result of calculation C.
I have created a measure that do the job using the ISINSCOPE() function and looks like this:
ConstantLine =
VAR ResultCountry =
MEDIANX(
CALCULATETABLE(
VALUES( 'Location'[Country] ),
ALL(
'Location'[Country],
'Location'[Region],
'Location'[City]
)
),
CALCULATE(
DISTINCTCOUNT( Sales[ID] ),
Sales[HasDiscount] = TRUE()
)
)
VAR ResultRegion =
MEDIANX(
CALCULATETABLE(
VALUES( 'Location'[Region] ),
ALL(
'Location'[Country],
'Location'[Region],
'Location'[City]
)
),
CALCULATE(
DISTINCTCOUNT( Sales[ID] ),
Sales[HasDiscount] = TRUE()
)
)
VAR ResultCity =
MEDIANX(
CALCULATETABLE(
VALUES( 'Location'[City] ),
ALL(
'Location'[Country],
'Location'[Region],
'Location'[City]
)
),
CALCULATE(
DISTINCTCOUNT( Sales[ID] ),
Sales[HasDiscount] = TRUE()
)
)
VAR Result =
SWITCH(
TRUE(),
ISINSCOPE( 'Location'[Country] ),
ResultCountry,
ISINSCOPE( 'Location'[Region] ),
ResultRegion,
ISINSCOPE( 'Location'[City] ),
ResultCity
)
RETURN
Result
My problem is that it simply doesn't work 😞
I mean the easure seems to work because when I use it as Tooltip on my scatter plot I see the correct value when I hover a point.
But when I use the measure as a dynamic constant line it returns an empty result. I tried to replace ISINSCOPE with ISFILTERED but the result is the same. So the measure is working but I can't find a way to make the ISINSCOPE work in the context of a constant line. Can someone help me with this? Is there any workaround?
