Forum Discussion
Multiple Conditions in OR Statement
I'm trying to modfiy a measure by adding a third condition to an OR statement, but have run into a slight problem! It looks like you're unable to have multiple conditions in an condition in an OR statement.
So here's the calculation:
CALCULATE( SUMX( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength]) , DimFlightLeg[OperatorCode] = "SY" , DimFlightLeg[Status] = 1 || DimFlightLeg[Status] = 5 || DimFlightLeg[Status] <> 1 && DimFlightLeg[DepartureDate] < 06/25/2019 ,
DimFlightLeg[Flight as a Number] < 8000 && NOT (DimFlightLeg[Flight as a Number] >= 1000 && DimFlightLeg[Flight as a Number] <= 1099 ) ,
USERELATIONSHIP( DimDateDeparture[Departure_Date], DimFlightLeg[DepartureDate]) ,
USERELATIONSHIP( DimFlightLeg[FlightLegID], FactPassengerLeg[FlightLegID] ) )
The issue is with the third condition in the OR statement. How can you have multiple conditions here? Would the || need to be replaced with the actual OR function? I'm kind of at a loss!
Any insight or help would be greatly appreciagted, as I'm fairly new to DAX!
Thanks!!
I noticed you had the hard-coded date value as "text" so I changed it to use the DATE function. I also changed the filter clauses to do what I think you are looking for.
ASMs Flown :=
CALCULATE (
SUMX ( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength] ),
DimFlightLeg[OperatorCode] = "SY",
FILTER (
ALL ( DimFlightLeg[Status], DimDimFlightLeg[DepartureDate] ),
OR (
DimFlightLeg[Status] IN { 1, 5 },
AND (
DimFlightLeg[Status] = 0,
DimFlightLeg[DepartureDate] < DATE ( 2019, 6, 19 )
)
)
),
DimFlightLeg[Flight as a Number] < 8000
&& NOT ( DimFlightLeg[Flight as a Number] >= 1000
&& DimFlightLeg[Flight as a Number] <= 1099 ),
USERELATIONSHIP ( DimDateDeparture[Departure_Date], DimFlightLeg[DepartureDate] ),
USERELATIONSHIP ( DimFlightLeg[FlightLegID], FactPassengerLeg[FlightLegID] )
)If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
9 Replies
Anonymous , as they are from one table, I put them into one filter
CALCULATE( SUMX( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength]) , filter(DimFlightLeg, DimFlightLeg[OperatorCode] = "SY" && ( DimFlightLeg[Status] = 1 || DimFlightLeg[Status] = 5 || DimFlightLeg[Status] <> 1 ) && DimFlightLeg[DepartureDate] < 06/25/2019 &&
DimFlightLeg[Flight as a Number] < 8000 && NOT (DimFlightLeg[Flight as a Number] >= 1000 && DimFlightLeg[Flight as a Number] <= 1099 )) ,
USERELATIONSHIP( DimDateDeparture[Departure_Date], DimFlightLeg[DepartureDate]) ,
USERELATIONSHIP( DimFlightLeg[FlightLegID], FactPassengerLeg[FlightLegID] ) )Use parentheses to combine OR inside one unit, whenever applicable. You can also use AND(<>,<>) , OR(<>,<>)
Thanks for the reply, but one issue: the last OR condition is one "whole" condition. So it's not that the status can be either 1, 5, or not equal to 1 and then evaluate the date. The third condition needs to meet the two criteria: status <> 1 AND date < 6/25/2019. Either status is 1, or status is 5, or status is not <>1 AND date < 6/25/2019.
Then after that OR is evaluated and the results are filtered, apply the other filter: DimFlightLeg[Flight as a Number] < 8000 && NOT (DimFlightLeg[Flight as a Number] >= 1000 && DimFlightLeg[Flight as a Number] <= 1099 )) .
Using your calc, it returns zero rows, which is not the expected result.
Does there need to be two filters?
Hi Anonymous ,
Try the following formula and check if it works:
OR ( DimFlightLeg[Status] IN { 1, 5 }, DimFlightLeg[Status] <> 1 && DimFlightLeg[DepartureDate] < 06 / 25 / 2019 )Thank you...but where does this code slice fit into the entire calculation?
This is the original measure:
CALCULATE(
SUMX( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength])
, DimFlightLeg[OperatorCode] = "SY"
, DimFlightLeg[Status] = 1 || DimFlightLeg[Status] = 5
, DimFlightLeg[Flight as a Number] < 8000 &&
NOT (DimFlightLeg[Flight as a Number] >= 1000 &&
DimFlightLeg[Flight as a Number] <= 1099
)
, USERELATIONSHIP( DimDateDeparture[Departure_Date], DimFlightLeg[DepartureDate])
, USERELATIONSHIP( DimFlightLeg[FlightLegID], FactPassengerLeg[FlightLegID] )
)So, I've tried a couple of different things, this being one of them:
ASMs Flown TEST:= CALCULATE( SUMX( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength]), FILTER(DimFlightLeg, DimFlightLeg[Status] = 1 || DimFlightLeg[Status] = 5),
FILTER( CROSSJOIN( ALL(DimFlightLeg[Status]), ALL(DimFlightLeg[DepartureDate]) ), DimFlightLeg[Status] <> 1 && DimFlightLeg[DepartureDate] < 06/25/2019 ) ,
DimFlightLeg[OperatorCode] = "SY" ,
DimFlightLeg[Flight as a Number] < 8000 && NOT (DimFlightLeg[Flight as a Number] >= 1000 && DimFlightLeg[Flight as a Number] <= 1099 ) ,
USERELATIONSHIP( DimDateDeparture[Departure_Date], DimFlightLeg[DepartureDate]) ,
USERELATIONSHIP( DimFlightLeg[FlightLegID], FactPassengerLeg[FlightLegID] ) )
In this case, the first FILTER returns a "table" that meets the OR condition. With that being said, the second FILTER is I'm assuming returning zero rows, because essentially no rows from the first filter match te second.
So, thought the CROSSJOIN would evaluate the data separately from the first FILTER, but it doesn't look like that's the case.
I essentially need all the rows that match my original OR and combine those rows with the data returned from the second or and THEN apply the last couple of filters on operator code and flight number.
