Adding 2 NOT IN VALUES to measure

[android1]

Hi,

How can I filter the below measure to also filter values from 2 other tables (vw_PivotWagesHrs & vw_Absence).

I want to add this filter to the Measure YTD Workforce running total in Weeknum (this measure is in table CarersList)

NOT ( ( 'CarersList'[CarerRef] IN VALUES ( 'vw_PivotWagesHrs'[CarerRef] ) ) && NOT ( ( 'CarersList'[CarerRef] IN VALUES ( 'vw_Absence'[CarerRef] ) )

YTD Workforce running total in Weeknum = CALCULATE ( [YTD Workforce], FILTER ( ALLSELECTED ( 'MyDateTable'[Weeknum] ), ISONORAFTER ( 'MyDateTable'[Weeknum], MAX ( 'MyDateTable'[Weeknum] ) - 1, DESC ) ) )

[v-danhe-msft]

Hi @android1,

You could refer to this formula:

YTD Workforce running total in Weeknum =

CALCULATE (
[YTD Workforce],
FILTER (
ALLSELECTED ( 'MyDateTable'[Weeknum] ),
ISONORAFTER ( 'MyDateTable'[Weeknum], MAX ( 'MyDateTable'[Weeknum] ) - 1, DESC )
&& NOT ( 'CarersList'[CarerRef] IN VALUES ( 'vw_PivotWagesHrs'[CarerRef] ) )
&& NOT ( 'CarersList'[CarerRef] IN VALUES ( 'vw_Absence'[CarerRef] ) )
)
)

Due to I could not figure out your data structure, I have tested with my own data like the picture below and it could work on my side:

Hope it could help you.

Regards,

Daniel He

[android1]

Hi @v-danhe-msft,

Thank you. 

With my data I'm getting 'A single value for column 'CarerRef' in table 'CarersList' cannot be determined.'

CareREF is a whole number column.