Populate missing data using last populated row

[Lew_14]

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 set of data logs always has this value missing and unfortunately there's no way i can apply a fix at source.

I require a new column that basically contains the following logic:

If the current rows' 'RefNo' field is blank, bring across the last logged value in 'RefNo' until the 'Value' resets to 0. I've tried a few different solutions but my DAX isn't up to scratch to get everything working.

Below is a snapshot of my data and i would expect a solution to populate rows 110-124 with 'REF001', after which a new production run ('REF002') begins (however i'm also seeing the same issue with data logs not being captured at the very start of the run, which isn't currently an issue considering what i'm trying to achieve).

Any help/guidance would be massively appreciated!

[Anonymous]

Hello @Lew_14 

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

[Lew_14]

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

[v-easonf-msft]

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]

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]

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.

[Lew_14]

Apologies, it's a little tricky to explain! Once the value resets to 0 a new count will begin for the next production run however the count can sometimes climb (to 10, for example) before the RefNo is allocated. In these cases i need the calculation to look forward and grab the next RefNo but i think your suggestion only does this where the value is 0, if i'm correct?

I'm struggling to verify this as i'm receiving an error that there isn't enough available memory to add the amended index column. My table only has 20k rows and around 10 columns and i have 8GB RAM available so if there's anything you can think of to make the code more efficient that would be extremely helpful.

Many thanks 

[v-easonf-msft]

Hi, @Lew_14 

Sorry, the question is a bit beyond my ability. I have no idea how to optimize this code of mine 😞.

I think there may be other more convenient ideas, but I haven't thought of it at the moment.

Best Regards,
Community Support Team _ Eason

[Anonymous]

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

Thank You.