Forum Discussion

jdata's avatar
jdata
Frequent Visitor
9 years ago
Solved

Filling Data Gaps, Conditionally

  Hi, I have a table that looks like the one below.  One of the data columns has some blanks that I would like to fill.  How can I fill downward, but only when the identifier (first column) of the '...
Tips and Tricks
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    jdata

     

    A Dax solution .

     

    Would be a new calculated column:

     

    NewD =
IF (
    Table1[D] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Table1[D], Table1[D] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[A ] ), Table1[B] <= EARLIER ( Table1[B] ) )
    ),
    Table1[D]
)

     

    The Columns Are A,B,C,D in the order of your sample data.. 

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

I like the offset index approach and the join.  I will use that for sure.  Much easier than what I butchered up.

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

Indeed.

 

I use it regularly in my solutions and if you've done it a few times, you have it arranged within 10 seconds with just a few clicks.

 

For 1 particular case I created a video some time ago, in which this is clearly illustrated during the first minute (after the first minute the video becomes too specific for that particular case).