Populate with first non blank

[BenjaminFab45]

Hi,

On my dataset, several devices have GPS positions. This positions are not on each line as my device don't moove but for my dashboard I need to populate each line with GPS position. What is the best formula to populate all the GPS positions with lastest ? (I want to find the first non blank position per device ID).

Dataset exampe :

DeviceID;Lattitude

A;null

A;null

A;15.00000

B;null

A;17.0000

B;null

B;20.000

The result must be :

A;15.00000

A;15.00000

A;15.00000

B;20.000

A;17.0000

B;20.000

B;20.000

I try several formula with "FIRSTNONBLANK", FILTER, EARLIER but don't arrived to find the good formula 😕

Can you help me please ?

Thanks a lot !

[Anonymous]

Try this  :

Measure =
CALCULATE ( FIRSTNONBLANK ( ALL ( Table1[Column2] ), TRUE () ) )

Column2 is "Numbers" columns.

Thanks

Aditya

[BenjaminFab45]

Thanks for your help, How I must do to "filter" by ID ? Because in this case, the formula don't care about the IDs.

This is the results :

[BenjaminFab45]

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

[BenjaminFab45]

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 ?

[BenjaminFab45]

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 ?

[nirvana_moksh]

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.

[BenjaminFab45]

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]
)

 

[BenjaminFab45]

This formula take account of the IDs, "issue" is possible if the device send two GPS positions at the same time (same second), in practice it's not possible or with retry mechanism. In this case the formula will take one of the positions, which will the same. (if I'm right)

LatCalc = if(ISBLANK(Table1[Content.Lat]);
VAR LastNonBlankDate =
CALCULATE(LASTNONBLANK(Table1[Content.Time];TRUE());FILTER(ALL(Table1);Table1[Content.DevID] = EARLIER(Table1[Content.DevID]) && Table1[Content.Time] < EARLIER(Table1[Content.Time]) && NOT (ISBLANK(Table1[Content.Lat]))))

VAR CurrID = Table1[Content.DevID]

RETURN CALCULATE(LASTNONBLANK(Table1[Content.Lat]; TRUE());FILTER(ALL(Table1);Table1[Content.Time] = LastNonBlankDate && Table1[Content.DevID] = CurrID));
Table1[Content.Lat]
)