Forum Discussion

FayeB1901's avatar
FayeB1901
Icon for Helper I rankHelper I
4 years ago
Solved

Report Last Non Blank Row as a column

Hi All 

 

Putting this one out there again as I wasn't able to get a working solution. I've got a measure to do this, but I need  a calculated column. This is because the data is latitudes which ARC GIS can't plot in a map without having an individual row context. 

 

Options include:

1. A calculated column
2. Possibly a summary table?

Any feedback appreciated. See summary table below showing the problem. The highlighted values are pulling the last non blank value in the data set so suspect there is some DAX missing to delineate between IDs, could it be that simple? 

Thanks
Faye

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You want the last nonblank prior to the date in the current row?

     

    Add an extra condition to your MaxDate calculation:

    Last Lat =
VAR CurrDate = Table1[Date]
VAR MaxDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        NOT ( ISBLANK ( Table1[Lat] ) ),
        Table1[Date] <= CurrDate
    )
RETURN
    CALCULATE (
        MAX ( Table1[Lat] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[Date] = MaxDate
    )

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    You can certainly get the last Lat per ID.

    Last Lat =
VAR MaxDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        NOT ( ISBLANK ( Table1[Lat] ) )
    )
RETURN
    CALCULATE (
        MAX ( Table1[Lat] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[Date] = MaxDate
    )

    The ALLEXCEPT here is specifying that we want to match only the ID column.

    • FayeB1901's avatar
      FayeB1901
      Icon for Helper I rankHelper I
      4 years ago

      Hmm close but not quite, this returns last non blank row overall, not the last non blank row based on the current row, if that makes sense?

       

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        You want the last nonblank prior to the date in the current row?

         

        Add an extra condition to your MaxDate calculation:

        Last Lat =
VAR CurrDate = Table1[Date]
VAR MaxDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        NOT ( ISBLANK ( Table1[Lat] ) ),
        Table1[Date] <= CurrDate
    )
RETURN
    CALCULATE (
        MAX ( Table1[Lat] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[Date] = MaxDate
    )