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 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 ) ) )

Need Help

2 Replies

  • 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.