Forum Discussion

DS1234's avatar
DS1234
New Member
1 year ago
Solved

Difference between markers / points / goalposts ?

Hi,

 

I have an line chart, where I am looking at values for each day. I am hoping for something similar to below, where I can put two "markers" or "points" or "goalposts" that are represented by the red lines noted "Value 1" and "Value 2".

Then, I would like the difference between those 2 points in both X (time) and Y (value).

 

Basically I am trying to analyze database growth, but we will occasionally archive data off, which is where you see the value suddenly drops. A regular trend-line fit won't work over the full period, but CAN work over a shorter period.

 

 

I DO understand that I can filter to a shorter date that covers the desired time window, but feel like something like above would let me quickly change the "markers" and see the growth rate over the different "smoother regions" between archiving activities.

 

Any thoughts?

 

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi DS1234 ,

    I created a sample pbix file(see the attachment), please check if that is what you want. 

    1. Remove the relationship between two dimension table and fact table

    2. Add secondary y-axis

    3. Add two X-Axis Constant Lines

    Best Regards

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi DS1234 -you can use measures and a dynamic slicer setup to calculate the differences in X (time) and Y (value) based on two selected markers (start and end points).

    Use these measures to calculate the values of the selected marker (Y-axis)

     

    StartDiskSpace =
    CALCULATE(
    MAX('DiskData'[DiskSpace]),
    'DiskData'[Date] = SELECTEDVALUE('StartDate'[StartDate])
    )

    EndDiskSpace =
    CALCULATE(
    MAX('DiskData'[DiskSpace]),
    'DiskData'[Date] = SELECTEDVALUE('EndDate'[EndDate])
    )

     

    Create measures to calculate the differences in time (X-axis) and value (Y-axis)

    ChangeInDiskSpace = [EndDiskSpace] - [StartDiskSpace]

    ChangeInDays =
    DATEDIFF(
    SELECTEDVALUE('StartDate'[StartDate]),
    SELECTEDVALUE('EndDate'[EndDate]),
    DAY
    )

    GrowthRate =
    DIVIDE([ChangeInDiskSpace], [ChangeInDays], 0) // Growth rate in MB/day

     

     

    Add vertical lines to your chart for the selected dates. You can use a Line and Stacked Column Chart visual with measures like this

     

    MarkerLine =
    IF(
    'DiskData'[Date] = SELECTEDVALUE('StartDate'[StartDate]) ||
    'DiskData'[Date] = SELECTEDVALUE('EndDate'[EndDate]),
    MAX('DiskData'[DiskSpace]),
    BLANK()
    )

     

    Add this measure as a line series to your chart to highlight the markers.

     

    Hope the above calculations helps to derive the same. 

    • DS1234's avatar
      DS1234
      New Member
      1 year ago

      Hi,

       

      Thank you and I really appreciate the response! However, I don't quite understand fully.

       

      I am not so clear on the "Dynamic Slicer" idea here. But what I could find, I ended up doing the following:

       

      1 ) Create a StartDate table defined as:

      StartDate =
      VAR MinDate = MIN(TableSpace[RunDate])
      VAR MaxDate = MAX(TableSpace[RunDate])
      RETURN
      ADDCOLUMNS(
          CALENDAR(MinDate, MaxDate),
          "Year", YEAR([Date]),
          "Day", DAY([Date])
      )
       
      2 ) Create an EndDate table defined as:
      EndDate =
      VAR MinDate = MIN(TableSpace[RunDate])
      VAR MaxDate = MAX(TableSpace[RunDate])
      RETURN
      ADDCOLUMNS(
          CALENDAR(MinDate, MaxDate),
          "Year", YEAR([Date]),
          "Day", DAY([Date])
      )
       
      3 ) Create relationships for my base data table's (TableSpace) date column to the 2 date tables in the model
       
      4 ) Define measure for Start Disk Space:
       
      StartDiskSpace =
      CALCULATE(
      SUM('TableSpace'[Used(MB)]),
      'TableSpace'[RunDate] = SELECTEDVALUE('StartDate'[Date])
      )
       
      5 ) Define measure for End Disk Space
       
      EndDiskSpace =
      CALCULATE(
      SUM('TableSpace'[Used(MB)]),
      'TableSpace'[RunDate] = SELECTEDVALUE('EndDate'[Date])
      )
       
      6 ) Add 2 slicers to the page:
      * StartDate is a slicer for "Date" column of StartDate table
      * EndDate is a slicer for "Date" column of EndDate table
      * It is weird from a UI side to not have a calendar for dates, but I had to make these dropdowns. Otherwise I cannot pick a SINGLE value, and the SELECTEDVALUE function does not work.
       
      7 ) At this point, I have to use "Edit Interactions" so that StartDate and EndDate do not filter the chart. Otherwise the chart is empty.
       
      8 ) Add 2 cards to the page:
      * one for StartDiskSpace, one for EndDiskSpace
      * I had use "Edit Interactions" again. EndDate slicer must NOT filter StartDiskSpace card, or the value is always (Blank). Similarly, StartDate slicer must NOT filter EndDiskSpace card, or the value is always (Blank).
       
      9) Add the measure ChangeInDays and add to a Card:
      * Note for this, I can only get the value to not be (Blank) if StartDate and EndDate slicers BOTH filter the card in "Edit Interactions"
       
      ChangeInDays =
      DATEDIFF(
      SELECTEDVALUE('StartDate'[Date]),
      SELECTEDVALUE('EndDate'[Date]),
      DAY
      )
       
      10 ) Add the measure ChangeInDiskSpace and add to a Card:
      * Note for this, no matter how I change the "Edit Interactions" the value is (Blank).
      * It seems since as mentioned in Step 8, I have to turn off some filters for the Cards to work, that the combination of those 2 means I cannot get both values at the same time no matter how the filters for ChangeInDiskSpace is configured.
      * I am stuck here.
       
      ChangeInDiskSpace = [EndDiskSpace] - [StartDiskSpace]
       
       
      So close! But the math for the measures will not work, so long as the filters compute one or the other values as (Blank).
  • DS1234's avatar
    DS1234
    New Member
    1 year ago

    Hi rajendraongole1 

     

    Sorry to bother, but any input on above approach? I am stuck on step 10 that I outlined. I'm going to try changing the DAX - something to do with filter context maybe. I'm not an expert at this and wondering if I made a misstep above.

     

    Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi DS1234 ,

      I created a sample pbix file(see the attachment), please check if that is what you want. 

      1. Remove the relationship between two dimension table and fact table

      2. Add secondary y-axis

      3. Add two X-Axis Constant Lines

      Best Regards

      • DS1234's avatar
        DS1234
        New Member
        1 year ago

        Hi Anonymous 

         

        Thank you! Yes, that is perfect. I appreciate you giving feedback on the points I messed up.

         

        Thank you!

        Doug