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

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 ?

  • nirvana_moksh's avatar
    nirvana_moksh
    Impactful 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]
        )