Forum Discussion

android1's avatar
android1
Icon for Post Patron rankPost Patron
8 years ago

Adding 2 NOT IN VALUES to measure

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 Weekn...
Need Help
v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    android1
    Icon for Post Patron rankPost Patron
    8 years ago

    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.