Forum Discussion

LurkingDude's avatar
LurkingDude
Frequent Visitor
1 year ago
Solved

Find Average Using Most Recent Date

Hi, all.

New to DAX (but with some database background) so have been struggling to solve a simple issue. I have a data model that tracks tournament results for chess players. Partial table structure noted below:

dCalendar
   Date
   ...

dMembers
   MemberID
   Expiry
   ...

fResults
   ResultDate
   MemberID
   NewRating
   ...

dCalendar and fResults are related by Date and ResultDate; dMembers and fResults are related by MemberID. For each uique MemberID in dMembers, there can be one or hundreds of entries in the fResults table--just depends how active that player is.

I'm trying to calculate the average member rating using ONLY each member's MOST RECENT entry in the fResults table. I would like to restrict this calculation to only active members where dMembers[Expiry] is greater than today.

I'm sure this is pretty starightforward, but the max(date) bit is throwing me off. Many thanks for any suggestions.

  • FreemanZ's avatar
    FreemanZ
    1 year ago

    hi AntrikshSharma ,

     

    yes, indeed. thank you very much for pointing this out. 

     

    LurkingDude 

    try like this instead:

    measure =
    VAR _table =
    ADDCOLUMNS(
        SUMMARIZE(
            CALCULATETABLE(
                fResults,
                dMembers[Expiry]>TODAY()
            ),
            dMembers[MemberID],
            dMembers[Expiry]
        ),
        "RecentDate",
        CALCULATE(MAX(fResults[ResultDate]))
    )
    VAR _result =
    CALCULATE(
        AVERAGE(fResults[NewRating]),
        TREATAS(
            _table,
           dMembers[MemberID],
           dMembers[Expiry],
           fResults[ResultDate]
        )
    )
    RETURN _result

9 Replies

    • LurkingDude's avatar
      LurkingDude
      Frequent Visitor
      1 year ago

      Thanks for the response. Yes, this will calculate the average rating for all non-expired members. But it does not take the most-recent rating only from the results table--this is the challenge I am having.

      Each active member may have literally hundreds of entries in the fResults table, each entry with a rating. I need to calculate the average on ONLY each member's most-recent rating.

      Thanks.

      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        1 year ago

        LurkingDude Define most recent, unless you have a column such as LastUpdateDate you can't quantify most recent.

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi LurkingDude ,

     

    try like:

     

    measure =

    VAR _table =

    ADDCOLUMNS(

        SUMMARIZE(

            fResults,

            dMembers[MemberID],

            dMembers[Expiry]

        ),

        "RecentDate",

        CALCULATE(MAX(fResults[ResultDate]))

    )

    VAR _result =

    CALCULATE(

        AVERAGE(fResults[NewRating]),

        TREATAS(

           CALCULATETABLE(

                _table,

                dMembers[Expiry]>TODAY()

           ),

           dMembers[MemberID],

           dMembers[Expiry],

           fResults[ResultDate]

        )

    )

    RETURN _result

    • LurkingDude's avatar
      LurkingDude
      Frequent Visitor
      1 year ago

      Whoa! No wonder I couldn't figure it out. LOL.

      Thanks for the response. I will try this out at first opportunity.

      So I understand what the code is doing, will the code line CALCULATE(MAX(fResults[ResultDate])) iterate over _table and return the max date for EACH MemberID? I don't see where the grouping by MemberID is happening. Is it in the Summarize function? (I'm not too familiar with that one.)

      Thanks for helping me understand.

      • FreemanZ's avatar
        FreemanZ
        Super User
        1 year ago

        hi LurkingDude ,

         

        yes, done with SUMMARIZE.

        the code is not verified, please come back and @me, in case of issue.

      • FreemanZ's avatar
        FreemanZ
        Super User
        1 year ago

        hi AntrikshSharma ,

         

        yes, indeed. thank you very much for pointing this out. 

         

        LurkingDude 

        try like this instead:

        measure =
        VAR _table =
        ADDCOLUMNS(
            SUMMARIZE(
                CALCULATETABLE(
                    fResults,
                    dMembers[Expiry]>TODAY()
                ),
                dMembers[MemberID],
                dMembers[Expiry]
            ),
            "RecentDate",
            CALCULATE(MAX(fResults[ResultDate]))
        )
        VAR _result =
        CALCULATE(
            AVERAGE(fResults[NewRating]),
            TREATAS(
                _table,
               dMembers[MemberID],
               dMembers[Expiry],
               fResults[ResultDate]
            )
        )
        RETURN _result