Forum Discussion

rosscortb's avatar
rosscortb
Post Patron
6 years ago

Measure that returns the current value

Hello,

 

Hoping you can help.

I have two tables, headcount and Submitted Approved.

I have used this measure to bring in the sector that the employees works in from the headcount table to the Submitted Approved table. I haven't used lookup as gives the error of to many values when one is expected.

 

Sector = CALCULATE(FIRSTNONBLANK(Headcount[Sector],1),
FILTER(Headcount,Headcount[SAP] = SubApproved[SAP ID]
))
 
This works fine, however, it seems to return the wrong value if an employee has changed sector at some point.
 
In the example below using the measure above these two employees are showing as 
SAP ID        Sector
1848321     Corp Mgt( should be Finance)
2638713     Corp Mgt
 
If I swap firstnonblank to lastnonblank if get
 
SAP ID        Sector
1848321     Finance(correct)
2638713     Thermal(should be Corp Mgt)
 
Any ideas?
Thanks Ross
Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    HI rosscortb ,

     

    If you have a date field in both table you should use it to make an additional filter on your values so that you can have the correct result at the correct time.

     

    Can you share some sample data?

    • rosscortb's avatar
      rosscortb
      Post Patron
      6 years ago

      Hi, I do have have two dates fields in the submitted and approved which is basically start and end date for annual leave so don't think its relevant

       

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi rosscortb ,

         

        If you don't have any start and end date how do you know the Sector of a specificic employee at a certain time?

         

        Can you share a sample data and setup of the relationships in you model?

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    Hi rosscortb 

     

    you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.