Forum Discussion

KeithHWU's avatar
KeithHWU
Frequent Visitor
5 years ago
Solved

Measure to return latest value based per ID

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 p...
Need Help
  • KeithHWU's avatar
    KeithHWU
    5 years ago

    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's avatar
AlB
Community Champion
5 years ago

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's avatar
KeithHWU
Frequent Visitor
5 years ago

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.