Filtering last cells combination in DAX?

[coen_]

Hi,

I'm trying to put generate a 'LastWarehouseOwner' list in a DAX formula using filtering but can't get it to work.
See below for an example of the database of our tracking system.

reference sequence Owner Department


12346 1 UserA Office
12346 2 UserA Warehouse
12346 3 UserD Warehouse
12346 4 UserC Service




Tracking system keeps process flow with unique reference numbers, so a combination of [reference + sequence] is unique as sequence always increases.
Owner changing randomly and departments may or may not be involved during flow so may not always exist.
I'd like to generate a list of the last 'Owner' (which is highest sequence number) if 'Warehouse' exists


So basically I need to provide a list for each reference who's the last owner in the warehouse. I know it can be done with advanced filtering etc. but I of course don't want to do it manually.
Any suggestions?

 

[Ozeroth]

Here's a possibility.
This will return the value of 'Owner' for the highest value of 'sequence' where Department = Warehouse.
In a PivotTable with 'reference' on the rows this should return what you're looking for.

Last Owner Where Department is Warehouse :=
CALCULATE (
    IF ( HASONEVALUE ( Tbl[Owner] ), VALUES ( Tbl[Owner] ) ),
    CALCULATETABLE (
        LASTNONBLANK ( Tbl[sequence], 1 ),
        Tbl[Department] = "Warehouse"
    )
)

You could add extra checks e.g. to only allow it to evaluate for a single reference.