Fill blanks based in two conditions

[MarceloSaez]

Hello Everybody. I'm new to Power BI and I'm trying to fill blanks, but I haven't found a solution in this forum that exactly matches my specific problem. I hope the following clarifies the problem:

My Data:

What I want to achieve:

I've tried the following (found here) but it doesn't work when it changes from "Producer" to "Injector":

StatusFilled = 
IF (
    Table[Status] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Table[Status], Table[Status] ),
        FILTER ( ALLEXCEPT ( Table, Table[Well] )Table[Date] <= EARLIER ( Table[Date] ) )
    ),
    Table[Status]
)

Any Ideas? Thanks in advance!!!

Regards,

Marce

[Icey · Accepted solution]

Hi @MarceloSaez ,

How about this?

Column =
VAR FirstnotblankIndex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> BLANK ()
                && 'Table'[Well] = EARLIER ( 'Table'[Well] )
                && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
        )
    )
VAR FirstnotblankStatus =
    CALCULATE (
        MIN ( 'Table'[Status] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> BLANK ()
                && 'Table'[Well] = EARLIER ( 'Table'[Well] )
                && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
        )
    )
RETURN
    IF ( 'Table'[Index] >= FirstnotblankIndex, FirstnotblankStatus )

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarceloSaez]

Thanks a lot! It worked!

Best regards!

[amitchandak]

@MarceloSaez , Try a new column like

StatusFilled = 
IF (
    Table[Status] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Table[Status], Table[Status] ),
        FILTER ( Table, Table[Well] = EARLIER ( Table[Well] )  && Table[Date] <= EARLIER ( Table[Date] ) )
    ),
    Table[Status]
)

[MarceloSaez]

@amitchandak Thank you for your reply! I tried your modification but it continues doing the same thing... it almost does what I want except when there is a second change in the column "Status" for the same "Well", as shown in the picture. I want that when the Status changes to "InjectorWF" to continue down, but instead it comes back to "Productor Primaria".

Thank you again, I'm learning a lot from the forum.

Regards!

Marce

[MarceloSaez]

I think I know what the problem is. The table comes initially from Power Query Editor, and is ordered using the index column.

but when apply changes, then the table becomes un-ordered, like in the following image:

I've tried using different sorting orders in Power Query Editor, but I always get the same result --> So LASTNONBLANK is working fine, but the problem is the order.

Any ideas why is this happening?

[MarceloSaez]

Hello again. I've been searching on this issue of Power BI not maintaining sort order when loading the query into the model. It seems like a memory optimization, which in large tables puts all the similar entries one after the other. This appears to be my case.

I haven't read any solutions to this change in the sort order, so apparently I can't overcome my problem using DAX ☹️. Is there a way of generating the StatusFilled column directly in the Power Editor, so that I have no need to do it in DAX?

Regards!

Marce