Forum Discussion
Populate with first non blank
I try this formula, I'm near of the goal, but not reach yet ! =D
Colonne = IF(ISBLANK(Feuil1[Column2]);CALCULATE( FIRSTNONBLANK(Feuil1[Column2]; TRUE()); FILTER(Feuil1;Feuil1[Column1] = EARLIER(Feuil1[Column1])));Feuil1[Column2])
Result :
The issue is for example with the first line "A / Null / 15", the result must be "A / Null / 21".
Do you know why I only get "15" and not the last value ?
Thanks
After some tests I think I need to sort my table with date (not visible on this sample dataset) because I Think power bi by default sort by..... something but not know which.
I'm right ? If yes, how I can sort data by date ?
I found this article : http://www.excelnaccess.com/using-firstnonblank-lastnonblank-in-dax/
It's almost the goal, just need to take care about the ID.
Do you know how I can do ?
Use the below DAX:
CALCULATE(LASTNONBLANK('TableA'[Table A Column Name],1),FILTER('TableA','TableA'[Index] = MIN('TableA'[Index])))
And in Power Query Editor define a Index Column which will serve as ID, then in the above DAX change MIN to MAX or vice versa based on your sort.
Thanks for your reply.
But with this DAX this does not take into account IDs (Column 1 in my screenshot : IDs : "A" and "B"), right ?
I try this DAX and seem to work but they have a issue if device send two ore more GPS positons at the same time... In this case I have the sum of all GPS position and he d'ont take in account the IDs, so if ID "A" and ID "B" send position at the same time, I will have an issue.
LatCalc = if(ISBLANK(Table[Content.Lat]);
VAR LastNonBlankKey =
CALCULATE(LASTNONBLANK(Table[Content.Time];TRUE());FILTER(ALL(Table);Table[Content.DevID] = EARLIER(Table[Content.DevID]) && Table[Content.Time] < EARLIER(Table[Content.Time]) && NOT (ISBLANK(Table[Content.Lat]))))RETURN CALCULATE(SUM(Table[Content.Lat]);FILTER(ALL(Table);Table[Content.Time] = LastNonBlankKey));
Table[Content.Lat]
)
