Forum Discussion

Lew_14's avatar
Lew_14
Frequent Visitor
4 years ago

Populate missing data using last populated row

I'm looking for some help!   I have a table that shows minute-by-minute data logs for machines in a production environment. Each row should have a 'RefNo' value however i'm finding that the last se...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello Lew_14 

You can create a calculated column. If RefNo is blank and value > 0 then Index else blank().

Lew_14's avatar
Lew_14
Frequent Visitor
4 years ago

I'm not sure how that would work, Tarun. Could you please explain further? I've previously added a calculated column that brings across the previous RefNo if the current RefNo is blank (using the Index, DAX below) but obviously that only works for the last populated row +1, whereas i need all unpopulated rows to be filled in with the RefNo for that particular run.

 

RefNo (amended) = if(ISBLANK('TABLE'[RefNo]),LOOKUPVALUE('TABLE'[RefNo],'TABLE'[Index],'TABLE'[Index]-1),'TABLE'[RefNo])
 
It could work by capturing the index of the last populated row of RefNo for any given run and then bring across that variable to any row where the RefNo is blank, however i'm not sure how i'd do this.
 
Many thanks
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Lew_14 
    Could you please share the pbix file after removing the sensitive information? Also, please share the expected outcome also.

    Thank You.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, Lew_14 

    Try calculated columns as below:

    Index (amended) = 
VAR a =
    CALCULATE (
        Max ( 'Table'[Index] ), 
        FILTER (
            'Table',
            'Table'[Index] <= EARLIER ( 'Table'[Index] )  
                && 
                'Table'[RefNo] <> BLANK () 
        )
    )  
RETURN
    IF ( ISBLANK ( 'Table'[RefNo] ), a, 'Table'[Index] )
    RefNo2 (amended) = 
IF (
    ISBLANK ( 'TABLE'[RefNo] ),
    LOOKUPVALUE ( 'TABLE'[RefNo], 'TABLE'[Index], 'Table'[Index (amended)] ),
    'TABLE'[RefNo]
)

    Best Regards,
    Community Support Team _ Eason

    • Lew_14's avatar
      Lew_14
      Frequent Visitor
      4 years ago

      Hi v-easonf-msft,

       

      Thankyou for your reply, this works perfectly...almost!

       

      I mentioned that there were blank RefNo's at the start of the run and this wasn't an issue however it seems i was wrong now i've impemented your suggestion. Once the count resets to 0 i'd like the RefNo to be remain blank (as it is currently bringing across the previous RefNo and this is altering some of my visuals incorrectly), however it would be perfect if the code could look forward and grab the next available RefNo once the 'Value' count resets to 0 and the RefNo is blank, if that's possible?

       

      Many thanks,

      Lewis

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi, Lew_14 

        Not fully sure what your want. Is this the result you want?

        If so, please modify the formula of 'Index (amended)' as below:

        Index (amended) = 
VAR a =
    CALCULATE (
        MAX ( 'Table 2'[Index] ),
        FILTER (
            'Table 2',
            'Table 2'[Index] <= EARLIER ( 'Table 2'[Index] )
                && 'Table 2'[RefNo] <> BLANK ()
        )
    ) 
VAR b =
    CALCULATE (
        MIN ( 'Table 2'[Index] ),
        FILTER (
            'Table 2',
            'Table 2'[Index] >= EARLIER ( 'Table 2'[Index] )
                && 'Table 2'[RefNo] <> BLANK ()
        )
    ) 
RETURN
    IF (
        ISBLANK ( 'Table 2'[RefNo] ),
        IF ( 'Table 2'[Valuew] = 0, b, a ),
        'Table 2'[Index]
    )

        Please check 'Table 2' in my attachment.

         

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.