Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
Thanks a lot! It worked!
Best regards!
@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]
)
@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
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?
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