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 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. 

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.

5 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity 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.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

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

    @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

    SU18_powerbi_badge

  • KeithHWU's avatar
    KeithHWU
    Frequent Visitor
    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.