Forum Discussion

BenjaminFab45's avatar
BenjaminFab45
Frequent Visitor
8 years ago

Populate with first non blank

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 th...
Need Help
BenjaminFab45's avatar
BenjaminFab45
Frequent Visitor
8 years ago

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's avatar
BenjaminFab45
Frequent Visitor
8 years ago

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