Table Relations - counting the missing rows

[Mardo]

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

[v-yuta-msft · Accepted solution]

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

[Anonymous]

SELECT * FROM 2018 cy

RIGHT JOIN 2017 ly

ON cy.id = ly.id

WHERE cy.id IS NULL

[Mardo]

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

[v-yuta-msft · Accepted solution]

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

[Mardo]

Hello Jimmy,

Thank you so much for your informative reply!

Actually I did already the same approach, however, I was't sure if that would give me corrects results. It seem that this solution is the only way to get this issue resolved.

I have got now another issue: I have a table that contains a column with a header "Employment Start Date". What I need to do is to calculate how long time have our employees been working for the company (until now!), do we have any DAX formula to calculate that?

Many thanks again,

Kindest regards,

Mardo

[Anonymous · Accepted solution]

@Mardo,

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.

[Mardo]

Hello Richard,

Thanx a lot for your reply.

Actually I have used the first DAX-formula, and it works well for me 🙂

Thanx again for you collaboration, it is most appreciated!

Kindest Regards,

Mardo