CODEWITHSUNDEEP
daxpowerpivotpowerquerym
C. Rush
C. Rush

Reputation: 62

LOOKUPVALUE based upon aggregate function in DAX

I need a calculated column (because this will be used in a slicer) that returns the employee's most recent supervisor.

Data sample (table 'Performance'):

EMPLOYEE    |   DATE        |   SUPERVISOR
--------------------------------------------
Jim         |   2018-11-01  |   Bob
Jim         |   2018-11-02  |   Bob
Jim         |   2018-11-03  |   Bill
Mike        |   2018-11-01  |   Steve
Mike        |   2018-11-02  |   Gary

Desired Output:

EMPLOYEE    |   DATE         |  SUPERVISOR  |   LAST SUPER
---------------------------------------------------------------
Jim         |   2018-11-01   |  Bob         |   Bill
Jim         |   2018-11-02   |  Bob         |   Bill
Jim         |   2018-11-03   |  Bill        |   Bill
Mike        |   2018-11-01   |  Steve       |   Gary
Mike        |   2018-11-02   |  Gary        |   Gary

I tried to use 

LAST SUPER =
LOOKUPVALUE (
    Performance[SUPERVISOR],
    Performance[DATE], MAXX ( Performance, [DATE] )
)

but I get the error:

Calculation error in column 'Performance'[]: A table of multiple values was supplied where a single value was expected.

After doing more research, it appears this approach was doomed from the start. According to this, the search value cannot refer to any column in the same table being searched. However, even when I changed the search value to TODAY() or a static date as a test, I got the same error about multiple values. MAXX() is also returning the maximum date in the entire table, not just for that employee.

I wondered if it was a many to many issue, so I went back into Power Query, duplicated the original query, grouped by EMPLOYEE to get MAX(DATE), matched both fields against the original query to get the SUPERVISOR on MAX(DATE), and can treat this like a regular lookup table. While it does work, unsurprisingly the refresh is markedly slower.

I can't decide if I'm over-complicating, over-simplifying, or just wildly off base with either approach, but I would be grateful for any suggestions.

What I'd like to know is:

  1. Is it possible to use a simple function like LOOKUPVALUES() to achieve the desired output?
  2. If not, is there a more efficient approach than duplicating the query?

Upvotes: 1

Views: 3496

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

The reason LOOKUPVALUE is giving that particular error is that it's doing a lookup on the whole table, not just the rows associated with that particular employee. So if you have multiple supervisors matching the same maximal date, then you've got a problem.

If you want to use the LOOKUPVALUE function for this, I suggest the following:

Last Super = 
VAR EmployeeRows =
    FILTER( Performance, Performance[Employee] = EARLIER( Performance[Employee] ) )
VAR MaxDate = MAXX( EmployeeRows, Performance[Date] )
RETURN
LOOKUPVALUE(
    Performance[Supervisor],
    Performance[Date], MaxDate,
    Performance[Employee], Performance[Employee]
)

There are two key differences here.

  1. I'm taking the maximal date over only the rows for that particular employee (EmployeeRows).
  2. I'm including Employee in the lookup function, so that it only matches for the appropriate employee.

For other possible solutions, please see this question:

Return top value ordered by another column

Upvotes: 1

Related Questions