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

  • jdata's avatar
    jdata
    Frequent Visitor
    9 years ago

    Thanks Vvelarde. 

     

    When I run this formula I am almost getting the solution: In the 5th row from the bottom, we should have a value of 6 in the New D column, but we are getting a null.  Why is this?   Thanks.

     

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      9 years ago

      jdata

       

      Can you check if the Last B don't have a space in the end, i think is not equal to other Bs

      • jdata's avatar
        jdata
        Frequent Visitor
        9 years ago

        Yes there was a space.  Nice catch Vvelarde!!

         

        Thank you!!

         

        Jonathan 

  • 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? 

  • 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!

  • shoeshirt's avatar
    shoeshirt
    Frequent Visitor
    6 years ago

    You are awesome-I had a totally different dataset but was able to use this DAX expression to fill data

    based on a start date.

    Thank you, thank you, thank you!!!

  • davidataylor's avatar
    davidataylor
    Frequent Visitor
    6 years ago

    Vvelarde , 3 years later and your solution helped me today.  Thank you.