Solved: Help WIth Moving FIRST NONBLANKROWS from 1 Column ... - Page 2

Anonymous · ‎03-03-2020

Greeting my fellow user,

From my table called Data, I currently trying to insert the first nonblank value from START to the same row as the firstnonblank value from FINISH which at the column RESULT. There are thousands of REQUEST NO. with multiple START and FINISH date inside each REQUEST NO. and also with different amount of space for each first nonblank value of START and FINISH.

I tried using
RESULT = IF( 'DATA'[FINISH] <> BLANK(),
CALCULATE(FIRSTNONBLANK('DATA'[START], 1), ALLEXCEPT('DATA', 'DATA'[RANK])),

BLANK() )

But to no avail. Would appreciate every feedback given for this problem.

Thanks for reading my question!

REQUEST NO.	RANK	START	FINISH	RESULT
1938084	1	2/2/2020
1938084	1
1938084	1
1938084	2		3/2/2020	2/2/2020
1938084	2
1938084	3	4/2/2020
1938084	4		10/2/2020	4/2/2020
1938084	4
1938084	4

az38 · ‎03-04-2020

@Anonymous

maybe column like this

RESULT_COL = 
var _lastFinish = CALCULATE(MAX(DATA[FINISH]),FILTER(ALLSELECTED(DATA),DATA[FINISH]<EARLIER(DATA[FINISH])))
RETURN
IF(DATA[FINISH] <> BLANK(), CALCULATE(FIRSTNONBLANK(DATA[START],1),FILTER(ALLSELECTED(DATA),DATA[START]>_lastFinish)), BLANK())

but be carefull with FIRSTNONBLANK as it returns first value as it stored and not mandatory it is the same with what you see

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Help WIth Moving FIRST NONBLANKROWS from 1 Column to another

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly