LOOKUPVALUE based upon aggregate function in DAX












1














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?






dax powerpivot powerquery m







share|improve this question



























    1














    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?






    dax powerpivot powerquery m







    share|improve this question

























      1












      1








      1







      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?






      dax powerpivot powerquery m







      share|improve this question













      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?






      dax powerpivot powerquery m




      dax powerpivot powerquery m






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 19 '18 at 17:40









      C. Rush

      408




      408
























          1 Answer
          1






          active

          oldest

          votes


















          1














          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






          share|improve this answer

















          • 1




            Thank you so much for the simple, yet thorough explanation! I got an error the first time I used your code, but I realized it was because the sample data I provided above was overly simplified. When I added another match criteria for an additional field that made it one-to-one, it worked. Thanks again!
            – C. Rush
            Nov 19 '18 at 18:31











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379990%2flookupvalue-based-upon-aggregate-function-in-dax%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          1














          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






          share|improve this answer

















          • 1




            Thank you so much for the simple, yet thorough explanation! I got an error the first time I used your code, but I realized it was because the sample data I provided above was overly simplified. When I added another match criteria for an additional field that made it one-to-one, it worked. Thanks again!
            – C. Rush
            Nov 19 '18 at 18:31
















          1














          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






          share|improve this answer

















          • 1




            Thank you so much for the simple, yet thorough explanation! I got an error the first time I used your code, but I realized it was because the sample data I provided above was overly simplified. When I added another match criteria for an additional field that made it one-to-one, it worked. Thanks again!
            – C. Rush
            Nov 19 '18 at 18:31














          1












          1








          1






          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






          share|improve this answer












          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







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 19 '18 at 18:06









          Alexis Olson

          12.6k21634




          12.6k21634








          • 1




            Thank you so much for the simple, yet thorough explanation! I got an error the first time I used your code, but I realized it was because the sample data I provided above was overly simplified. When I added another match criteria for an additional field that made it one-to-one, it worked. Thanks again!
            – C. Rush
            Nov 19 '18 at 18:31














          • 1




            Thank you so much for the simple, yet thorough explanation! I got an error the first time I used your code, but I realized it was because the sample data I provided above was overly simplified. When I added another match criteria for an additional field that made it one-to-one, it worked. Thanks again!
            – C. Rush
            Nov 19 '18 at 18:31








          1




          1




          Thank you so much for the simple, yet thorough explanation! I got an error the first time I used your code, but I realized it was because the sample data I provided above was overly simplified. When I added another match criteria for an additional field that made it one-to-one, it worked. Thanks again!
          – C. Rush
          Nov 19 '18 at 18:31




          Thank you so much for the simple, yet thorough explanation! I got an error the first time I used your code, but I realized it was because the sample data I provided above was overly simplified. When I added another match criteria for an additional field that made it one-to-one, it worked. Thanks again!
          – C. Rush
          Nov 19 '18 at 18:31


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid …



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.





          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


          Please pay close attention to the following guidance:


          • Please be sure to answer the question. Provide details and share your research!

          But avoid …



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379990%2flookupvalue-based-upon-aggregate-function-in-dax%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          MongoDB - Not Authorized To Execute Command

          Image
          .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box; } 0 I have successfully enabled authorization on MongoDB and I have created an account on the admin database and then I created an account for my database called test. The following connection string to connect to my test database works successfully: mongo --host 192.168.17.52 --port 27017 -u user1 -p password --authenticationDatabase test Only problem I have now is, I cannot execute commands such as: show dbs. I get the following error when I try to do so: "errmsg" : "not authorized on admin to execute command { listDatabases: 1.0, lsid: { id: UUID("a1d5bc0d-bc58-485e-b232-270758a89455") }, $db: "admin...
          Read more

          in spring boot 2.1 many test slices are not allowed anymore due to multiple @BootstrapWith

          Image
          1 I tried to upgrade a yummy sandwich made of two test slices (@JsonTest and @JdbcTest in my case, crunchy test code in between) adding spring boot 2.1 flavour to it. But it seems it was not much of a success. I cannot annotate my tests with many @...Test since they are now each bringing their own XxxTestContextBootstrapper. It used to work when they all used same SpringBootTestContextBootstrapper. @RunWith(SpringRunner.class) @JdbcTest @JsonTest public class Test { @Test public void test() { System.out.printn("Hello, World !"); } } The error I get from BootstrapUtils is illegalStateException : Configuration error: found multiple declarations of @BootstrapWith for test class I understand I might be doing something wrong here but is there an easy way I could load both Json and Jdbc contex...
          Read more

          Npm cannot find a required file even through it is in the searched directory

          Image
          up vote 0 down vote favorite I got that error: users-Air:pdfuploader user$ npm run start client@0.1.0 start /Users/user/Documents/pdfuploader react-scripts start Could not find a required file. Name: index.html Searched in: /Users/user/Documents/pdfuploader/public npm ERR! code ELIFECYCLE npm ERR! errno 1 npm ERR! client@0.1.0 start: `react-scripts start` npm ERR! Exit status 1 npm ERR! npm ERR! Failed at the client@0.1.0 start script. npm ERR! This is probably not a problem with npm. There is likely additional logging output above. npm ERR! A complete log of this run can be found in: npm ERR! /Users/user/.npm/_logs/2018-11-19T10_19_14_555Z-debug.log users-Air:pdfuploader user$ And the file is in /Users/user/Documents/pdfuploader/public as you can see in my github repository: https://github.c...
          Read more