Return results that are in 1 table but not in 2 others.

[android1]

Hi,

I'm really struggling with this.

Off Sick & Not Paid =
CALCULATE (
    DISTINCTCOUNT ( Vw_Absence[CarerRef] ),
    NOT ( ( 'Vw_Absence'[CarerRef] IN VALUES ( 'vw_PivotWagesHrs'[CarerRef] ) ) )
)

Above measure  shows all people who are in table Vw_Absence but not in vw_PivotWagesHrs.

What I need to show are the people who are not in table Vw_Absence & not in table vw_PivotWagesHrs

but are in table Carerslist.

I'd like this filtered by WEEKNUM in the MyDateTable.

See sample file -> https://www.dropbox.com/s/e2a99fy0kw11ip7/Carer%20Attrition%20Sample%202.pbix?dl=0

[v-yuta-msft · Accepted solution]

Hi android1,

Modify you measure like this and check if it can work:

Off Sick & not paid modified =
CALCULATE (
    DISTINCTCOUNT ( 'CarersList'[CarerRef] ),
    NOT ( 'CarersList'[CarerRef] IN VALUES ( 'vw_PivotWagesHrs'[CarerRef] ) ),
    NOT ( 'CarersList'[CarerRef] IN VALUES ( 'Vw_Absence'[CarerRef] ) )
)

Regards,

Jimmy Tao

[android1]

Hi @v-yuta-msft,

Thank you very much.