Dynamic vertical/ Y axis line in Power BI

August 29, 2019

 

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

 

 

 

 

 

We will also create a Summary Orders table like below and establish a relationship between this table and the calendar table

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

Create another What-If parameter called DynamicDate2 following steps in the previous paragraph and create a new measure DynamicDateFormat2

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

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.

 

 

 

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

 

Please reload

Recent Posts

Please reload

Archive

Please reload

Tags

Please reload