DAX Measure With Multiple FILTERS

[Anonymous]

I'vw been grappling with how to write the DAX for a measure tht has a column that needs multiple filters. Here is my current DAX for the measure in question:

Booked Total Pax Legs:=CALCULATE( SUM( FactPassengerLeg[PassengerLegUnit] ) ,

FILTER( DimFlightLeg, DimFlightLeg[OperatorCode] = "SY") ,

FILTER( DimFlightLeg, DimFlightLeg[Flight as a Number] < 8000 ))

I also need to filter out on a range of values for [Flight as a Number], the range being [Flight as a Number] >=1000 and <=1099.

I've tried the && and the || arguments, but can't seem to get it right. I've tried the following:

Booked Total Pax Legs:=CALCULATE( SUM( FactPassengerLeg[PassengerLegUnit] )
, FILTER( DimFlightLeg, DimFlightLeg[OperatorCode] = "SY")
, FILTER( DimFlightLeg, DimFlightLeg[Flight as a Number] < 8000 )
, FILTER( DimFlightLeg, DimFlightLeg[Flight as a Number] < 1000 && DimFlightLeg[Flight as a Number] > 1099 ))

This measure essentially returns 0 because the filters filter everything out.

So, I'm just trying to filter on:

All flights with operator code of "SY"

All flights with a flight number of less than 8000. But also need to exclude all flights with a flight number between 1000 and 1099. Not sure how to pull this off.

Any insight would be awesome.

Thanks!!

[OwenAuger]

Hi @Anonymous 

Generally it's best to apply filters to columns rather than entire tables.

In your case you have filters to apply DimFlightLeg[OperatorCode] and DimFlightLeg[Flight as a Number].

I would rewrite your measure something like this:

Booked Total Pax Legs :=
CALCULATE (
    SUM ( FactPassengerLeg[PassengerLegUnit] ),
    DimFlightLeg[OperatorCode] = "SY",
    DimFlightLeg[Flight as a Number] < 8000
        && NOT ( DimFlightLeg[Flight as a Number] >= 1000
        && DimFlightLeg[Flight as a Number] <= 1099 )
)

If you need these filters to be be intersected with existing filters, you could wrap each filter argument in KEEPFILTERS.

Regards,

Owen