Implementing Fill Up and Fill Down in DAX

In this blog let's take a look at how you can Fill blank cells with values before or after them also know as Fill Up and Fill Down in various tools.

Here is the data and result in the same picture.

As you can see from the image, we want to fill down Value of EndOfDayRate at Index = 3 to rows at Index 4, 5, and 6 as show in Fill Down column, and same goes for Fill Up, we want Value of Index = 14 at rows 13 and 12 as shown in Fill Up column.

To begin with we need a column that would create a Unique Index on the table, it can be done in PowerQuery or It can be a unique numeric column that can be used for ORDER BY operation, if not possible in PowerQuery this can be implemented in DAX as well with the following code:

Rn uses ROWNUMBER to add Index, by using MATCHBY we are providing enough columns that can guarantee that the combinations of those columns makes every row unique, this is a necessary consideration in DAX's WINDOW functions.

Next we need to define Clusters that will start from a non empty cell and continue till next non empty cell, so from the above image as you can see after Rn = 3 we have 3 blanks so we want a Cluster Number that start at Rn = 3 and continues till Rn = 6 and then resets at Rn = 7, which can be obtained with the following code.

In this code we first select those columns that are to be used for deciding the Cluster, we use EndOfDayRate and RowNumber (Rn), then by using WINDOW function we create a Rolling Window based on the ORDERBY on Rn, we also use MATCHBY to ensure there is uniqueness in each row and by using COUNTX we basically count how many numbers are there in the rolling window since Rn = 1.

Now by using this column we can do Fill Down, and here are couple of different methods.

In this version, we get the Table for the current cluster, here ALLEXCEPT will work just like PARTITIONBY once we have that table we can use that inside INDEX to get the first row in the table based on the Rn ASC and once we have this row we can select the EndOfDayRate column using SELECTCOLUMNS, and since INDEX will always return a single row we can be assured that we don't need to use CONCATENATEX here as an error trapping mechanism.

Now this version is a bit convoluted and over the top, this is actually the first version I wrote but then I realised we don't need WINDOW functions here, the TableCurrentCluster variable has all that we need it has the EndOfDayRate and Blanks so why not just use MAXX to get the only value this cluster has?

Now I am sure this should work in every scenario but if doesn't then we can always fall back to WINDOW functions.

Finally we can do all of that only with the WINDOW functions as well:

In this version instead of ALLEXCEPT we rely on PARTITIONBY for getting the current cluster.

To implement the Fill Up, the logic is still the same you only need to tweak the Cluster so that this time Rn is ordered in DESC order.

And then you can use the same DAX formulas with this new cluster.

Now you can implement this logic in a Calculated Table as well but there is a limitation in MATCHBY that it can't use columns created in DAX on the fly.

So even though you can create Rn in Calculated Table you still need to use other columns in MATCHBY.

Or you can create Rn column first in the original table and then use that in MATCHBY and ORDERBY in Calculated Table, or you can create a Calculated Table that is just a reference to original table and then on this Table you can create Calculated Columns.

Doing this as a Measure is also fairly simple since we now already know the logics.