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

EF's avatar
EF
Helper II
6 years ago

Hi Vvelarde ,

I am trying to use this code. It is working to fill blanks with data from earlier rows of Column D (from your example) connected to the correct ID (column A), however it is not taking it from the most recent date (column B). All the blanks are being filled with the same value from one earlier date. I need each blank to be filled with the value from the row above (where ID is the same and Date is equal or earlier).

Example:

Column: A B C D

              A, 1/1/18,Q,9

              A,2/3/18,Q,8

              B,12/3/17,Q,5

              B,1/1/18,Q,2

              B,3/3/19,Q,4

              A,1/1/20,Q,null

              B,1/1/19,Q,null

 

Expected outcome for column D

              A, 1/1/18,Q,9

              A,2/3/18,Q,8

              B,12/3/17,Q,5

              B,1/1/18,Q,2

              B,3/3/19,Q,4

              A,1/1/20,Q,8

              B,1/1/19,Q,4

 

However I am getting random fill in such as:

              A, 1/1/18,Q,9

              A,2/3/18,Q,8

              B,12/3/17,Q,5

              B,1/1/18,Q,2

              B,3/3/19,Q,4

              A,1/1/20,Q,9

              B,1/1/19,Q,2

 

Any advice?

 

Thanks!