Forum Discussion

MarceloSaez's avatar
MarceloSaez
Icon for Helper I rankHelper I
6 years ago
Solved

Fill blanks based in two conditions

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

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      MarceloSaez
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        MarceloSaez
        Icon for Helper I rankHelper I
        6 years ago

        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?

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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.