Distinct Row Count

[saver7890]

Hi,

Newbie here. I have encountered difficulties in figuring out how to calculate distinct rows from two columns. 

I have two tables(see figure):

1. 'Ward_Bed' and its respective admission and discharge date;
2. The number of 'Admitted' (N= 385) and 'Discharge' (N = 355) within the distinct list of 'DATE' (N = 394).

I use the "manage relationships" function by "DOA" of the two tables and my final matrix as follows.

My intention is to compute the distinct countrow of the distinct count of DOA and DOD. 

Although the distinct count of totals are correct, the column value within the matrix are identical between DATE and DOA. 

I expect the Pediatric Ward to have 50 count under DATE instead of 34 after remove duplication of the appending DOA and DOD by excel. 

Thank you in advance for your help.

[saver7890]

Update with sample data.

https://docs.google.com/spreadsheets/d/1asJJq71M1E2DXQw8ctwMzGoA4np8deOp/edit?usp=sharing&ouid=10435...

The closest I get to work by using the following DAX:  

CALCULATE(DISTINCTCOUNT('inpatient service day'[DOA]),'inpatient service day'[DOA]<>BLANK()) + CALCULATE(DISTINCTCOUNT('inpatient service day'[DOD]),'inpatient service day'[DOD]<>BLANK())

Only two manage to get the right count. 

Only two manage to get the right count. 

It might has to do with two columns calculated as distinct count, ended up with 2 count. I want the 18/2/2020 counted as 1. How can this be achieved?   

Thank you all in advance with any guidance would be appreciated.

[amitchandak]

@saver7890 , Based on what I got, check if the approach having a date table and active inactive relation and using userelationship can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[saver7890]

Thank you for your attention. 

Yes, there is a date table and active relation. 

I tried your DAX syntax but the value is still off.

For example, the VIP ward should have 4 distinct counts as the "DOA" and "DOD" have different dates.

Thank you.