Forum Discussion
Table Relations - counting the missing rows
Hello,
I have created two different tables from the main CUBE. The first table contains 1544 rows and shows the employee in 2017. The second table has 1804 rows that shows the number of employees in 2018. (The number of employees is increasing!).
Now, my question is: HOW to determine the number of employees who left OR joined the company??? We have Employee-ID as a key in both tables. Please anyone could help?
Thanks in advance,
Regards,
Mardo
Hi Mardo,
So your requirement is equal to achieve rows only appear in table1 and rows only appear in table2, right?
Merge(Left Anti and Right Anti) can meet your requirement, click query editor->Merge Queries->Select join kind as left anti or right anti.
After merged, you get two tables that contains only people left the company and only people joined the company.
Finally, apply and create a measure using count(table) to calculate the number of people.
Regards,
Jimmy Tao
You should be able to use the following;
https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax
https://msdn.microsoft.com/en-us/query-bi/dax/today-function-dax
This will calculate the date you need.
6 Replies
Hello Richard,
Thank you for your reply.
I am just wondering if that code was a DAX-code?? I would need a DAX-formula to compare the values (User-ID) in the two columns from the two tables (to determine what is missing?)
I hope you understand my point?
Kindest regards,
Mardo
Hi Mardo,
So your requirement is equal to achieve rows only appear in table1 and rows only appear in table2, right?
Merge(Left Anti and Right Anti) can meet your requirement, click query editor->Merge Queries->Select join kind as left anti or right anti.
After merged, you get two tables that contains only people left the company and only people joined the company.
Finally, apply and create a measure using count(table) to calculate the number of people.
Regards,
Jimmy Tao
