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

JustinDoh1 — Thu, 14 Mar 2024 06:59:45 GMT

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.

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

lbendlin — Thu, 14 Mar 2024 16:59:30 GMT

it's very similar syntax.

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

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

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

JustinDoh1 — Thu, 14 Mar 2024 18:58:41 GMT

lbendlin

Thank you for your help again.

Does this look good?

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

lbendlin — Sun, 17 Mar 2024 23:15:40 GMT

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.

topic Re: How to exclude certain value within Filter (illustrated on the bottom DAX)? in DAX Commands and Tips

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

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

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

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