Help with DAX to deduct data from two tables based on ID& Date

[PiotrCalka]

Hello,

I have two tables:

- Annual Plans with columns: project_id, employee_id, Date 

- Leaves with columns: employee_id, Date 

There is no direct relatinoship between tables. Both have realtion to Calendar table and ID employee table.

I am counting DISTINCTCOUNT(Annual_Plans[employee_id]) - to see number of employees,

I need to see number of employees dedecuted by those who are on break (Leaves), so Annual Plans - Leaves per project id.

It needs to be sth like: If dates and employee_id from two tables equals then deduct Annuals Plans - Leave.

I would be gratefull for tips how to do it in DAX.

[PiotrCalka]

Thank you all,

Thank you @tamerj1 this work as I wanted.

[v-rongtiep-msft]

Hi @PiotrCalka ,

Could you please provide more details? Your pbix file without privacy inforamtion and desired output with screenshots.

How to Get Your Question Answered Quickly 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1 · Accepted solution]

Hi @PiotrCalka 

please try

=
COUNTROWS (
EXCEPT ( VALUES ( Annual_Plans[employee_id] ), VALUES ( Leaves[employee_id] ) )
)

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523