Measure to return latest value based per ID

[KeithHWU]

I have a table with multiple actions (ActIDKey) per Prospect (CnActLink). As follows:

I would like to create a measure that returns the text value (ActType) for the latest date (ActDate) per prospect (CnActLink) but only where the prefix is "FR" (excluding the SS).

My intention is that this measure will be used in a Table visualisation to compare the last FR action with another field called Prospect status for each prospect which can be filtered by a slicer for the account manager. Looking to highlight where these dont match. 

I'ma bit stuck. Have tried CALCULATE and LOOKUPVALUE so far but the multiple filters seem to cause me errors every time. 

[KeithHWU · Accepted solution]

Thanks to both of you for your responses. For future viewers of this post - I was struggling with DAX for this issue as I did not have a distinct entry per day. I ended up solving this in Power Query instead by duplicating the table of actions and grouping by person ID and max date and deduping. I was able to use this new table of Last Action to create my visual whilst still linking it to my dimaension tables. Probably an inefficient solution but it worked.

[AlB]

@KeithHWU

Most likely that means there is more than one Table1[ActType] value matching the conditions and so DISTINCT ( Table1[ActType] ) returns several values instead of just one

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

[Fowmy]

@KeithHWU 

Check this measure please:

Measure = 
var _MaxDate = MAX(Table3[ActDate]) return

MAXX(
    FILTER(
        Table3,
        Table3[ActDate] = _MaxDate && LEFT(Table3[ActType],2) = "FR"
    ),
    Table3[ActType]
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[AlB]

Hi @KeithHWU 

1. Place CnActLink on a table visual

2. Place this measure in  the visual

Measure =
VAR lastD_ =
    CALCULATE ( MAX ( Table1[ActDate] ), LEFT ( Table1[ActType], 2 ) = "FR" )
RETURN
    CALCULATE (
        DISTINCT ( Table1[ActType] ),
        Table1[ActDate] = lastD_,
        LEFT ( Table1[ActType], 2 ) = "FR"
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[KeithHWU]

Thanks @AlB ,

I'm receiving this error when displaying this visual and selecting between Acct Mngrs in my slicer:

Error Message:
MdxScript(Model) (59, 9) Calculation error in measure 'Measures Table'[LastMoveAIB]: A table of multiple values was supplied where a single value was expected.