Forum Discussion

paulorojog's avatar
paulorojog
Regular Visitor
4 years ago
Solved

Get last value from another table

Hi!

 

I'm struggling to get a matrix on my dashboard that shows the last project that an employee has been assigned.

 

For this I have two tables. The first one is Employee, that has IDEmployee, NameEmployee and other data, and a second table  Projects, with fields IDProject, IDEmployee, ProjectName, ProjectClient, ProjectMonth (an integer), etc.

 

So, one Employee is asigned to differents projects. I have built a matrix on my dashboard with the list of all employees and other data from this table and then I tried to add the ProjectName choosing Last, but this has returned the last by name sorted, so not necessary is the last project that the employee had been assigned.

 

I need the last record added or last project based on current month. If the employee doesn't have any project this month, it should return null or blank.

 

Thank you for your help in advance!

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, paulorojog ;

    You could modify it .

    Last client = 
var _table=SUMMARIZE(FILTER('NSR',EOMONTH([Periodo],0)=EOMONTH(TODAY(),0)),[Numero Profesional])
return IF(NOT( MAX('Dotación'[Nº pers.])) in _table ,BLANK(),
         CALCULATE(max(NSR[Cliente]),LASTNONBLANK('Calendar'[Date],CALCULATE(max(NSR[Cliente])))))
    Last project = 
var _table=SUMMARIZE(FILTER('NSR',EOMONTH([Periodo],0)=EOMONTH(TODAY(),0)),[Numero Profesional])
return IF(NOT( MAX('Dotación'[Nº pers.])) in _table ,BLANK(), 
          CALCULATE(max(NSR[Texto]),LASTNONBLANK('Calendar'[Date],CALCULATE(max(NSR[Texto])))))

    The final output is shown below:


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • paulorojog's avatar
    paulorojog
    Regular Visitor
    4 years ago

    Hi, 

     

    You should be able to download a sample of the document in this link.

     

    Here you have a reduced sample of the file. There are three tables:

     

    • Dotacion: table of employees
    • NSR: table of projects
    • Utilization: table of time dedicated to projects per month per employee

    As I said, I need to create a summary matrix that for each employee I can see their utilization (that's OK) and the last project and client they were assigned. If in that month, the employee was not assigned to any project, it should appear in blank. The problem is that if I add the project name (text field) and client, the current matrix brings the last ordered by name. 

     

    Thank you!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      To know the last project and client they were assigned to is not difficult because you have a Date column in the NSR table - so that can be done.  However, in the utilisation table, there is no Date column.  So this table would not respond to a Year/Month that we select in a slicer.  Therefore, the utilisation figure in your matrix visual will appear incorrect.

      FYI, to know the last project and client, you may use the LASTNONBLANK() function. 

      • paulorojog's avatar
        paulorojog
        Regular Visitor
        4 years ago

        Could you be more specific with your proposal? Is your suggestion I use LASTNONBLANK() as a column in a table or as a measure. If it is in a table, which table do you suggest? Employee table? And if it is Employee table, how I call the other table with that function.

         

        Should be the expression LASTNONBLANK('NSR'[Text], Month('NSR'[Date]) = Month(ReportMonth))? 

         

        Thank you!

  • paulorojog's avatar
    paulorojog
    Regular Visitor
    4 years ago

    Thank you! Your solution is the expected behavior. The only thing is still missing is how I could get with this that if an employee does not have a project assigned in the current month, the columns should be in blank.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      You are welcome.  Drag Month from the Calendar Table into a slicer and select a certain month.  Do you not get the expected result?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, paulorojog ;

    You could modify it .

    Last client = 
var _table=SUMMARIZE(FILTER('NSR',EOMONTH([Periodo],0)=EOMONTH(TODAY(),0)),[Numero Profesional])
return IF(NOT( MAX('Dotación'[Nº pers.])) in _table ,BLANK(),
         CALCULATE(max(NSR[Cliente]),LASTNONBLANK('Calendar'[Date],CALCULATE(max(NSR[Cliente])))))
    Last project = 
var _table=SUMMARIZE(FILTER('NSR',EOMONTH([Periodo],0)=EOMONTH(TODAY(),0)),[Numero Profesional])
return IF(NOT( MAX('Dotación'[Nº pers.])) in _table ,BLANK(), 
          CALCULATE(max(NSR[Texto]),LASTNONBLANK('Calendar'[Date],CALCULATE(max(NSR[Texto])))))

    The final output is shown below:


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.