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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

RicPT's avatar
RicPT
Regular Visitor
2 years ago

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!

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.