Compliance Check on EmpNo

[NoelGrieve]

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!

[amitchandak · Accepted solution]

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()))

[Icey]

Hi @NoelGrieve ,

Is this problem solved?

Best Regards,

Icey

[NoelGrieve]

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

Thanks again!

[harshnathani · Accepted solution]

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!!

[amitchandak · Accepted solution]

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()))