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

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?

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

        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