Forum Discussion

naitiksoni's avatar
naitiksoni
Regular Visitor
9 years ago
Solved

Reference Line on x-axis

I have date on my x-axis of a Line chart. I need a vertical reference line on a particular (dynamic) date to see if my trend line crossed that date or not.   Thanks.
  • BhaveshPatel's avatar
    BhaveshPatel
    9 years ago

    Hi naitiksoni

     

    To draw the Vertical reference line is currently not possible. You can use bit of trick though. Use Shapes to create a static vertical line and it is not dynamic as explained. See the attached screenshot.

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi naitiksoni,

     

    A vertical reference line is not available now. To work around this requirement, you can try to use a combination chart.

    1. Create a "line and stacked column chart"
    2. Put column [date] into shared axis and put [values] into line values.
    3. Create a measure that evaluates to the maximum value when Date=specific date(otherwise blank), put this measure into column values.

    Measures can be:

    Reference line =
    IF (
        HASONEVALUE ( Table1[Date] ),
        IF (
            VALUES ( Table1[Date] ) = DATE ( 2016, 3, 1 ),
            MAXX ( ALL ( Table1[Date] ), MAX ( Table1[Sales] ) )
        )
    )

     

    Thanks,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi naitiksoni,

 

A vertical reference line is not available now. To work around this requirement, you can try to use a combination chart.

  1. Create a "line and stacked column chart"
  2. Put column [date] into shared axis and put [values] into line values.
  3. Create a measure that evaluates to the maximum value when Date=specific date(otherwise blank), put this measure into column values.

Measures can be:

Reference line =
    IF (
        HASONEVALUE ( Table1[Date] ),
        IF (
            VALUES ( Table1[Date] ) = DATE ( 2016, 3, 1 ),
            MAXX ( ALL ( Table1[Date] ), MAX ( Table1[Sales] ) )
        )
    )

 

Thanks,
Yuliana Gu

ashishmenkudale's avatar
ashishmenkudale
Regular Visitor
7 years ago

Is there a way to get multiple vertical reference lines?

 

I tried this,

 

Reference line =
IF (
HASONEVALUE( Query1[datetime] ),
IF (
VALUES ( Query1[datetime] ) = DATESBETWEEN(Query2[DATE], DATE(2018,1,1), DATE(2018,12,31)),
MAXX ( ALL ( Query1[datetime] ), MAX ( Query1[param1] ) )
)
)

I get further error,

 

Error Message:

MdxScript(Model) (7, 43) Calculation error in measure 'Query1'[Reference line]: A table of multiple values was supplied where a single value was expected.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    curious to see if you ever got this working.

     

    I have a list of events that I'd like to reference as points or bars in my graphs.  Keep thinking that this original formula for static ref points 

    Reference line = 
    IF (
        HASONEVALUE ( 'Date'[Date] ),
        IF (
            VALUES ( 'Date'[Date] ) = DATE ( 2019, 03, 03),
            MAXX ( ALL ( 'Date'[Date] ), MAX ( WeeklyAnalysis[Actuals_CY] ) )
        )
    )
    should work.  I'd like to replace that date with a connection to the Events[startdate] column somehow, but trying with Max or Min on that doesn't seem to like it.
    • Matt22365's avatar
      Matt22365
      Resolver III
      7 years ago

      Hi Anonymous 

       

      No, Unfortunately I didnt manage to get it to work

       

      Matt