Forum Discussion

cosmicyes's avatar
cosmicyes
Helper II
3 years ago
Solved

Constant Line in column chart does not work with measure using ISINSCOPE

I thought this would be easy but it isn´t 😉

 

I have a stacked column chart where you can drill up and down from date / week / month / year.
What I need is a constant line for showing a goal depending on if week or month... or whatever is chosen.

I have created a measure:

 

 

GOAL = 
SWITCH(
    TRUE(),
    ISINSCOPE(Kalender[Date]),10000,
    ISINSCOPE(Kalender[Week]), 50000,
    ISINSCOPE(Kalender[Month]), 200000,
    ISINSCOPE(Kalender[YEAR]),2400000
)

 

 

Then I created a Constant line and added the measure using the fx-icon
(Format style "Field value", What field should we base this on? "Goal")


The constant line appears but the value is always 0, no matter what I drill through.

 

Just to test my measure I have changed the visual to Line and stack column chart and used the measure for Y-Axis - there it works! But this is not what I want.

Any ideas? Thank you in advance!

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi cosmicyes ,

    I don't mean for you to create a fixed line value. I just want to explain why the line always stays on 0, because constant line can't tell which level the X-axis is at. So, Line and stack column chart maybe the only way you can do it.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi cosmicyes ,

    Because for constant line, it can't tell which level the X-axis is at. If you add a ELSE value in the SWITCH formula, it can return that value like below.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • cosmicyes's avatar
      cosmicyes
      Helper II
      3 years ago

      Dear v-yanjiang-msft ,
      thank you for your input.
      But this way I always have the same constant line, no matter if week, month or whatever is selected. This is unfortunately not what I need.

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi cosmicyes ,

        I don't mean for you to create a fixed line value. I just want to explain why the line always stays on 0, because constant line can't tell which level the X-axis is at. So, Line and stack column chart maybe the only way you can do it.

         

        Best Regards,
        Community Support Team _ kalyj

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.