How to exclude certain value within Filter (illustrated on the bottom DAX)?

[JustinDoh1]

I have a Measure that has DAX code something like this:

Occupancy % Total Rest =

Divide(

    calculate(

            sum(tblTrend[Ave Census]),

            FILTER ( tblTrend, tblTrend[FacilityCode] in

               VALUES('tblKey'[FacilityCode])

                )

    ),  

    calculate(

            sum(tblOccupancy[Capacity]),

             FILTER ( tblTrend, tblTrend[FacilityCode] in

                 VALUES('tblKey'[FacilityCode]))

)

)

I would like to add more excluded value of something like tblKey'[FacilityCode] = " 61" to it or tblKey'[FacilityCode] = " 61" or "52"  along with VALUES('tblKey'[FacilityCode]) on certain Measures.

The reason that I am trying to do is, currently, this measure dynamically changes value by how user selects slicer called "Facility Name".

But, I would like to modify the calculation of this measure by adding another condition to exclude value(s).

In SQL, it would be like:

Where tblKey'[FacilityCode] not in ('61') or even like

Where tblKey'[FacilityCode] not in ('61', '52') on other future scenario where multiple values need to be excluded.

When I use slicer "Facility Name", and I want to show all Facilities (Valley, Ally, Jack) on rest of visuals, but I would like to apply this exclusion on only certain Measures.

How do I express that?

I have attached PBI file here for reference:

This particular CARD (that shows 91.9%) is the code (measure called "Occupancy % Total Rest") shown on the top.

So, in this case, instead of 91.9%, I would like to see 80.9% because FacilityCode of 61 (Jack) is excluded.

Thank you.

[lbendlin]

it's very similar syntax.

tblKey[FacilityCode] not in {"61", "52"}

or

tblKey[FacilityCode] not in VALUES(<enumerator>)  

[JustinDoh1]

@lbendlin 

Thank you for your help again.

Does this look good?

[lbendlin · Accepted solution]

looks ok-ish. I don't remember if these tables are joined in the data model?

By the way, instead of IN VALUES() you can also use TREATAS.  Allegedly that is a little faster.