Forum Discussion
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:
I'm using the following DAX expression
filledColumn=
Everytime the last non blank cell of "value" has a smaller value than the previous one, it keeps filling the new column with the older value; it will only update if a bigger number than the previous fill number appears on a row of "value . Can you help me with this? is there an alternative way of doing it using dax? Thank you so much!
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.
7 Replies
- johnbasha33
Super User
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 !!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.
Hi RicPT ,
First of all, many thanks to johnbasha33 for your very quick and effective replies.
Based on my testing, please try the following alternative formula:
1.Create the simple table.
2.Create the new measure to fill empty space.
FilledColumn = VAR index_ = SELECTEDVALUE('Table'[Index]) RETURN CALCULATE( LOOKUPVALUE('Table'[Value], 'Table'[Index], MAX('Table'[Index])), FILTER( ALL('Table'), 'Table'[Index] <= index_ && NOT(ISBLANK('Table'[Value])) ) )3.Drag the measure 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.
Thank you for your help. I confirmed it works in table visualization but i was trying something else. Most likely i'm not doing the right thing, but my interest was to fill the column in table view. I will try a different logic for this. Thank you for your time!
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.
- HarishKM
RicPT Hey,
you can use this feature of Power query. I am sharing a link with you to have a look.Fill values in a column - Power Query | Microsoft Learn
above feature will give the similer result and less load.Thanks
Harish
If I solve this issue Kindly give kudos and accept this solution.Thank you for your help! It's great in PowerQuery but not available in table view 🙂 Thank you for your time. Have a nice weekend!
