topic Re: Help with DAX to deduct data from two tables based on ID& Date in DAX Commands and Tips

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

PiotrCalka — Fri, 16 Sep 2022 12:45:00 GMT

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.

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

lbendlin — Sat, 17 Sep 2022 19:27:55 GMT

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-p/963216
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

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

tamerj1 — Sat, 17 Sep 2022 20:58:17 GMT

Hi PiotrCalka

please try

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

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

Anonymous — Mon, 19 Sep 2022 02:33:45 GMT

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.

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

PiotrCalka — Mon, 19 Sep 2022 09:25:54 GMT

Thank you all,

Thank you tamerj1 this work as I wanted.