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 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 !

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Try this  :

     

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

     

     

    Column2 is "Numbers" columns.

     

     

    Thanks

    Aditya

    • BenjaminFab45's avatar
      BenjaminFab45
      Frequent Visitor
      8 years ago

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

        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