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.. 

Vvelarde's avatar
Vvelarde
Community Champion
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.. 

Deepakshi's avatar
Deepakshi
New Member
8 years ago

Hi Vvelarde : Thanks for posting the dax solution online. I was trying to use the same solution for the exact same problem, but my lastnonblank value doesn't change for the same value in column A. For example: In Column D my 2 doesn't change to 3 for A in Column A, it remains 2 even though there's a new value 3 for A. I didn't change anything in the query. I am applying the query to a another calculated column though which uses the same solution to fill up conditionally and that works fine. Any idea what could be going on?