Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

1 ACCEPTED SOLUTION

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

9 REPLIES 9
v-eachen-msft
Community Support
Community Support

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
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

It's been almost two weeks...wondering if you were going to be able to reply to my last question regarding your response: How does that fit into the existing measure?

Still trying to get this to work.

Thanks

Anonymous
Not applicable

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] )
)

Anonymous
Not applicable

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.

 

 

 

Anonymous
Not applicable

In all of my research on this, I've found several people have run into the same problem, and it wasn't a syntax error, but a limitation with what you can do with regards to OR statements and the number of columns allowed. Everything I've seen says that a condition in an OR can only reference ONE column.

So, this calculation works fine:

ASMs Flown:=  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, pretty straightforward. Now, I try to add that third || condition where I also want to evaluate DimFlightLeg on Status AND DepartureDate. Give me everything where the OperatorCode is "SY" and where the Staus is either 1 OR 5 OR the status is 0 AND the DepartureDate is less than 6/19/2019:

ASMs Flown:=  CALCULATE( 
	SUMX( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength])
    , DimFlightLeg[OperatorCode] = "SY"	
    , DimFlightLeg[Status] = 1 || DimFlightLeg[Status] = 5 || DimFlightLeg[Status] = 0 && DimFlightLeg[DepartureDate] < "06/19/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] )
)

If this were plain SQL, thtis would be fine, but this calculation throws an error:

Measure 'Test Measures'[ASMs Flown] : The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

 

Even if you enclose the third OR condition in parantheses, it still throws the same error.

I thought of using FILTER, but that would return the wrong results, as FILTER's are iterative (I think that's the right verbage), and each consecutive FILTER would reference the previous FILTER statement. So doing a FILTER on Status = 1 would return all rows where the status = 1, and the next FILTER on Sttus = 5 would return nothing since the prior result set only contains rows where status = 1.

I'm just trying to do a simple "compound" OR that thas one condition with two columns. If this were SQL, it would be a slam dunk literally:

SELECT.......FROM DimFlightLegWHERESTATUS = 1
ORSTATUS = 5
OR
(STATUS = 0 AND DEPARTUREDATE < '06/19/2019')

 

Again, I'm pretty new to DAX, so sorry...but I can't believe it's this difficult? Or is it? No idea!

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Thank you!!

 

This is EXACTLY what I was looking for!

 

Awesome1

amitchandak
Super User
Super User

@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(<>,<>)

Anonymous
Not applicable

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?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.