Forum Discussion

genaussie's avatar
genaussie
Frequent Visitor
2 years ago
Solved

Best Practice for Max Calculation with a Condition

Hi All,

 

The current formula is relatively simple and works to return the latest Office for the Project based on the Period, but it involves using both LOOKUPVALUE and MAX. I'm wondering if there is a cleaner way that is considered best (or better) practice:

 

Formula =
    LOOKUPVALUE('List'[Office],
        'List'[Project], 'List'[Project],
        'List'[Period], CALCULATE(MAX('List'[Period]), ALLEXCEPT('List', 'List'[Project])))

Thanks in advance for any insight.
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi genaussie 

    Try this:

    CalcColumn =
VAR MaxPeriodByProject =
    CALCULATE ( MAX ( 'List'[Period] ), ALLEXCEPT ( 'List', 'List'[Project] ) )
RETURN
    CALCULATE (
        MAX ( 'List'[Office] ),
        FILTER (
            ALL ( 'List' ),
            'List'[Period] = MaxPeriodByProject
                && 'List'[Project] = EARLIER ( 'List'[Project] )
        )
    )

4 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi genaussie 

    Try this:

    CalcColumn =
VAR MaxPeriodByProject =
    CALCULATE ( MAX ( 'List'[Period] ), ALLEXCEPT ( 'List', 'List'[Project] ) )
RETURN
    CALCULATE (
        MAX ( 'List'[Office] ),
        FILTER (
            ALL ( 'List' ),
            'List'[Period] = MaxPeriodByProject
                && 'List'[Project] = EARLIER ( 'List'[Project] )
        )
    )
    • genaussie's avatar
      genaussie
      Frequent Visitor
      2 years ago

      Hi danextian confirming this works. It is useful to have alternative solutions ğŸ™‚

      Another option is to utilise 2x VAR:

      VAR MaxPeriodByProject = CALCULATE ( MAX ( 'List'[Period] ), ALLEXCEPT ( 'List', 'List'[Project] ) )

      VAR CurrentProject = 'List'[Project]

      RETURN

          CALCULATE (

              MAX ( 'List'[Office] ),

              FILTER (

                  ALL ( 'List' ),

                  'List'[Period] = MaxPeriodByProject

                      && 'List'[Project] = CurrentProject )

              )

  • elitesmitpatel's avatar
    elitesmitpatel
    Solution Supplier
    2 years ago

    Try this

    LatestOfficeByPeriod =
    VAR CurrentProject = SELECTEDVALUE('List'[Project]) -- Get the currently selected project
    RETURN
    LOOKUPVALUE(
    'List'[Office],
    'List'[Project], CurrentProject,
    'List'[Period], EARLIER('List'[Period])
    )

    • genaussie's avatar
      genaussie
      Frequent Visitor
      2 years ago

      Hi, I don't think this works as I get the error message, "EARLER/EARLIEST refers to an earlier row context which doesn't exist."