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 t...
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

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper 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).