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
nirvana_moksh's avatar
nirvana_moksh
Icon for Impactful Individual rankImpactful Individual
8 years ago

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