Brisbane, 4000 QLD

+61 4 3836 7017

Dynamic vertical/ Y axis line in Power BI

by | Jan 21, 2020 | Power BI | 0 comments

In this post I will showcase how to set up dynamic vertical line in Power BI and use it to explore your dataset.
The Adventure dataset is used in this example. In the Calendar table create a new column called DateInNumberFormat as below
DateInNumberFormat =
VALUE ( dimCalendar[DateID] )
We will also create a Summary Orders table like below and establish a relationship between this table and the calendar table

SummaryOrders =
SUMMARIZE (
fctSales,
dimCalendar[DateID],
“OrdersGrouped”, SUM ( fctSales[OrderQuantity] )
)

Next create a What-If parameter called DynamicDate1 with the minimum and maximum from the DateInNumberFormat column like below and add it to the page. A new tabled DynamicDate1 with DynamicDate1 Column and DynamicDate1 Value measure will be created.
Next create a measure called DynamicDateFormat1 and place it in the same table. This will lookup the date value based on the number in the DateInNumberFormat column
DynamicDateFormat1 =
LOOKUPVALUE (
dimCalendar[DateID],
dimCalendar[DateInNumberFormat], [DynamicDate1 Value]
)
Create another What-If parameter called DynamicDate2 following steps in the previous paragraph and create a new measure DynamicDateFormat2
DynamicDateFormat2 =
LOOKUPVALUE (
dimCalendar[DateID],
dimCalendar[DateInNumberFormat], [DynamicDate2 Value]
)
Now that we have set up our parameter tables we will start creating new measures to use these parameters. Create two measures to create the vertical lines
OrderVerticalLine1 =
IF (
VALUE (
SELECTEDVALUE ( dimCalendar[DateID] ) ) = [DynamicDate1 Value],
300,
BLANK ()
)
OrderVerticalLine2 =
IF (
VALUE (
SELECTEDVALUE ( dimCalendar[DateID] ) ) = [DynamicDate2 Value],
300,
BLANK ()
)
The maximum orders per day in the dataset is 198 so we have chosen 300 so that the lines extend way beyond the maximum.

Next create these four measures

OrderQuantityGreen =
VAR p1 =
CALCULATE (
SUM ( SummaryOrders[OrdersGrouped] ),
FILTER (
ALL ( dimCalendar[DateID] ),
dimCalendar[DateID] = [DynamicDateFormat1]
)
)
VAR p2 =
CALCULATE (
SUM ( SummaryOrders[OrdersGrouped] ),
FILTER (
ALL ( dimCalendar[DateID] ),
dimCalendar[DateID] = [DynamicDateFormat2]
)
)
RETURN
SWITCH (
TRUE (),
p2 > p1, CALCULATE (
SUM ( SummaryOrders[OrdersGrouped] ),
FILTER (
SummaryOrders,
SummaryOrders[DateID] >= [DynamicDateFormat1]
&& SummaryOrders[DateID] <= [DynamicDateFormat2]
)
),
p2 < p1, BLANK ()
)

OrderQuantityRed =
VAR p1 =
CALCULATE (
SUM ( SummaryOrders[OrdersGrouped] ),
FILTER (
ALL ( dimCalendar[DateID] ),
dimCalendar[DateID] = [DynamicDateFormat1]
)
)
VAR p2 =
CALCULATE (
SUM ( SummaryOrders[OrdersGrouped] ),
FILTER (
ALL ( dimCalendar[DateID] ),
dimCalendar[DateID] = [DynamicDateFormat2]
)
)
RETURN
SWITCH (
TRUE (),
p2 < p1, CALCULATE (
SUM ( SummaryOrders[OrdersGrouped] ),
FILTER (
SummaryOrders,
SummaryOrders[DateID] >= [DynamicDateFormat1]
&& SummaryOrders[DateID] <= [DynamicDateFormat2]
)
),
p2 > p1, BLANK ()
)

The logic here is to define the values at the beginning of the selected period and at the end of the selected period. If the end value is greater than the beginning value the line is formatted as green else its formatted as red. I have used a simple difference to compare values but you can you other metrics like variance, slope etc.

OrdersGroupedMeasure1 =
IF (
SELECTEDVALUE ( dimCalendar[DateID] ) <= [DynamicDateFormat1],
SUM ( SummaryOrders[OrdersGrouped] ),
BLANK ()
)

OrdersGroupedMeasure2 =
IF (
SELECTEDVALUE ( dimCalendar[DateID] ) <= [DynamicDateFormat2],
SUM ( SummaryOrders[OrdersGrouped] ),
BLANK ()
)

These measures will make sense when we place them in our visual.Place a line and stacked column chart on a report page and drag the measures so that it looks like this
Change the colour of the line for OrderQuantityGreen to Green and OrderQuantityRed to Red.

Next place the DynamicDate1 and DynamicDate2 as slicers and have them as single values and place them above the line and stacked column chart. Below them place the Card visual and drag the measures DynamicDateFormat1 and DynamicDateFormat2 into them. The final layout should look like this

Interact with the slicers and see how the middle section changes colour here
Share this

Submit a Comment

Your email address will not be published. Required fields are marked *