Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX Measure With Multiple FILTERS

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

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    6 years ago

    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