In order to show a line that builds over time, we first need to understand how Power BI plots points out of the box. You can think of a line chart in Power BI as all of the points within the current filter context of the visual. In other words, if we have a year slicer narrowing our year range down to 2005 through 2009, our line chart will only show the years 2005, 2006, 2007, 2008, and 2009. This makes sense. But in order to achieve a chart that shows the points for all of the years up to a point, we need to write some code.
To set up the demo and really nail down the ideas in the previous paragraph, let’s create a basic line chart using Year as the X-Axis and any metric (Unemployment Rate) in this example as the Y-Axis.
Let’s then install the Play Axis custom visual from the custom marketplace. If we through our Year field into the Play Axis slicer and click the run button, we will successfully see how Power BI handles the situation.
The Play Axis slicer is simply stepping through each year and filtering the line chart down to the single year selected in the slicer. So how do we get around this? The answer: A disconnected table. We can create another table that does not have any relationships set up to our other data tables. We will then write a single measure to ensure that our line will grow over time, as opposed to constantly being filtered down to a single year.
Our disconnected table will include all the years available to us in our data. For our data, years 2000 through 2018 are present. We simply need to click on the Modelling tab, click New Table, and then type the following code:
Selected Year = DISTINCT(Master[Year])
Our new table will be called “Selected Year” and will contain the distinct values of our “Year” column from out “Master” table. Change the names as necessary to fit your data.
With this new table in place, place the Year column from the Selected Year table into the Play Axis slicer. If we now click the play button, you will see that the line chart is unaffected. This makes sense as there isn’t a relationship set up from our Master table to our Selected Year table.
The final step is to create a measure that will filter our line chart to only show years that are less than or equal to the year in the play axis slicer. The code is simple, yet tricky to get your head wrapped around.
Max Year =
SWITCH(
TRUE(),
MAX(Master[Year]) <= MAX(‘Selected Year'[Year]),
1,
0
)
The logic in the measure is simply seeing if the year on the X-Axis is less than or equal to the year in the Play Axis slicer. For example, the first year in our data is the year 2000. So when our Play Axis slicer is showing 2000, our Max Year measure will ask: Which years on the X-axis are less than or equal to 2000? Only 2000 fits this description. So the year 2000 gets a 1 and the other years receive a 0. When the Play Axis slicer is on the year 2005, the years 2000 through 2005 will receive a 1, while 2006 and onward will take a 0.
Now we just need to place this measure in the filters of our line chart and set it equal to one, as in the following picture.
From there, all you need to do is hit PLAY! Our chart will now constantly filter down to all years below the year selected in slicer. Pretty neat trick and very quick to implement.
Make sure you check out my other tricks here and on the BI Elite YouTube Channel.