Forum Discussion

NoelGrieve's avatar
NoelGrieve
Frequent Visitor
6 years ago
Solved

Compliance Check on EmpNo

Hi Everyone, 

 

I am a little stuck, I am sure it is something really simple, I just cannot figure it out or I am trying to overcomplicate it. Hope you can help.

 

I have an Employeetable with Empno. and their Start date which is my Dim table, I then have another system to measure onboarding completed and date with their Empno. (the fact table). All I need to do is check whether the record exists in the fact table. What is the best way to do this? I am stuck in the excel vlookup thinking.

 

I have done a merge in Power Query but I do not think this is the right way?

 

Thanks!

 

 

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    You can so it using lookupvalue or maxx in this case

     

    New column in Employee1

    Onboarding= maxx(filter(onboarding,onboarding[Emp ID] = emp[Emp ID] ),onboarding[onboarding Date])

     

    Other examples

    New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

    New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name])

     

    https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
    new Column =
    LOOKUPVALUE('Shops Products'[Lot No.], 'Shops Products'[Entry Type], "Output", 'Shops Products'[Document No.], firstnonbank('Shops Products'[Document No.],true()))

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi NoelGrieve,

     

    You can also try the below function to Create a Table:

     

    Table = Filter(NATURALINNERJOIN('Employee Table','Onboarding Table'),'Employee Table'[Employee No] = 'Onboarding Table'[Emp ID ])
     
     
    Thanks,
    Harsh Nathani
     
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    You can so it using lookupvalue or maxx in this case

     

    New column in Employee1

    Onboarding= maxx(filter(onboarding,onboarding[Emp ID] = emp[Emp ID] ),onboarding[onboarding Date])

     

    Other examples

    New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

    New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name])

     

    https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
    new Column =
    LOOKUPVALUE('Shops Products'[Lot No.], 'Shops Products'[Entry Type], "Output", 'Shops Products'[Document No.], firstnonbank('Shops Products'[Document No.],true()))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi NoelGrieve,

     

    You can also try the below function to Create a Table:

     

    Table = Filter(NATURALINNERJOIN('Employee Table','Onboarding Table'),'Employee Table'[Employee No] = 'Onboarding Table'[Emp ID ])
     
     
    Thanks,
    Harsh Nathani
     
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
    • NoelGrieve's avatar
      NoelGrieve
      Frequent Visitor
      6 years ago

      Thank you! I will give both options a shot, but I am sure both will work!

       

      Thanks again!