Forum Discussion

RicPT's avatar
RicPT
Regular Visitor
2 years ago
Solved

Problem with filling empty spaces in column

I'm having a problem with PowerBI dax. I have a table with two columns, Index and Value, and i need a new column that is filled with the last non blank value of column Value, as shown next:   ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi RicPT ,

    Based on my testing again, please try the following DAX formula:

    1.Create the new column and enter the following DAX formula.

     

    filledcolumn = 
var index_ = 'Table'[Index]
RETURN
CALCULATE(
    LASTNONBLANK('Table'[Value], TRUE()),
    FILTER(
        ALL('Table'),
        'Table'[index] <= index_
    )
)

     

    2.Drag the column into the table visual. The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

johnbasha33's avatar
johnbasha33
Super User
2 years ago

RicPT 

It seems like you want to fill a new column with the last non-blank value from the "Value" column, but you're encountering issues when the last non-blank value is smaller than the previous one. This is because your current DAX expression doesn't consider the order of values correctly.

To achieve the desired result, you can modify your DAX expression to consider the order of rows based on the "Index" column. Here's how you can do it:

```DAX
filledColumn =
VAR LastNonBlankValue =
CALCULATE (
LASTNONBLANK ( 'Table'[Value], TRUE () ),
FILTER ( ALL ( 'Table' ), 'Table'[index] <= EARLIER ( 'Table'[index] ) )
)
RETURN
CALCULATE (
MAXX ( FILTER ( 'Table', 'Table'[index] <= EARLIER ( 'Table'[index] ) ), 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), 'Table'[Value] = LastNonBlankValue )
)
```

This expression calculates the last non-blank value from the "Value" column and then finds the maximum value of "Value" up to the current row where the "Value" is equal to the last non-blank value. This ensures that the filled column gets updated with the latest value if it's greater than the previous one.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

RicPT's avatar
RicPT
Regular Visitor
2 years ago

Thank you so much for the help. Unfortunately, the expression MAXX ( FILTER ( 'Table', 'Table'[index] <= EARLIER ( 'Table'[index] ) ), 'Table'[Value] ),  always results on an error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist." as it doesn't recognize the input 'Table'[index]. This only happens in the MAXX filter, not in the FILTER ( ALL ( 'Table' ), 'Table'[index] <= EARLIER ( 'Table'[index] ) ). Thanks again for the time you've all taken to help me.