Forum Discussion

DAX_n00b's avatar
DAX_n00b
Frequent Visitor
2 years ago
Solved

Get latest value not working with duplicates

Hi all,

 

Sorry for starting another similar thread but I couldn't find anything among the existing posts.

 

The standard approach
= VAR maxDate = MAXX(Table, Table[Date])
RETURN
MAXX(
FILTER(Table, Table[Date] = maxDate),
Table[Status]
)

doesn't seem to work since there are numerous duplicates in the Date column (no time stamp, date only).

Since records in the source table are added chronologically, I literally need the latest non-blank value related to given tool.

 

The output pivot should look like this:

Can you advise, please?

Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    If you add an Index column in the query editor you can then use that to build a measure like,

    lastestStatus =
    var _maxIndex =
    VALUE(
        MAXX(
            FILTER('Table', 'Table'[Status] <> ""),
            'Table'[Index]
        )
    )
    Return
    LOOKUPVALUE(
        'Table'[Status],
        'Table'[Index],
        _maxIndex
    )
     
    Where the max index value for non blank statuses is calculated and then used in a lookup formula. 
    The tool id context is added in the visual.
    E.g.

     

2 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    If you add an Index column in the query editor you can then use that to build a measure like,

    lastestStatus =
    var _maxIndex =
    VALUE(
        MAXX(
            FILTER('Table', 'Table'[Status] <> ""),
            'Table'[Index]
        )
    )
    Return
    LOOKUPVALUE(
        'Table'[Status],
        'Table'[Index],
        _maxIndex
    )
     
    Where the max index value for non blank statuses is calculated and then used in a lookup formula. 
    The tool id context is added in the visual.
    E.g.

     

    • DAX_n00b's avatar
      DAX_n00b
      Frequent Visitor
      2 years ago

      Working like a charm, thanks a million!