# Complex Filter In Measure

• ### Question

• I apoogize in advance if this post is somewhat pedestrian, but I'm fairly new to DAX, so I can use all the help I can get!

I currenty have a measure which uses an OR clause and a couple of filters. I need to add another criteria to the OR clause, or find a way to rewrite the filters.

Here is the current calculation:

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

What I need to do is add another condition to the OR, but I don't think that's possible. I need a filter like this:

``````DimFlightLeg[Status] <> 1 && DimFlightLeg[DepartureDate] < 06/25/2019
``````

From what I understand, you can't have multiple tables in an OR, so I'm guessing a filter would be required.

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.

Any help would be really appreciated!

Thanks!

A. M. Robinson

Sunday, August 9, 2020 2:32 AM

• So, I finally managed to find the answer to my issue:

```ASMs Flown TEST:=
CALCULATE (
SUMX ( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength] ),
DimFlightLeg[OperatorCode] = "SY",
FILTER (
ALL ( DimFlightLeg[Status], DimFlightLeg[DepartureDate] ),
OR (
DimFlightLeg[Status] IN { 1, 5 },
AND (
DimFlightLeg[Status] = 0,
DimFlightLeg[DepartureDate] < DATE ( 2019, 6, 25 )
)
)
),
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] )
)
```

A. M. Robinson

• Marked as answer by Monday, August 24, 2020 1:01 AM
Monday, August 24, 2020 1:01 AM

### All replies

• Why we can't have multiple tables in OR operator , from what I know there is no such restrict , did you get any error by doing so ?

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !

Monday, August 10, 2020 7:29 AM
• You can only use one column in an OR ( || ). You cannot do th following:

`DimFlightLeg[Status] = 1 || DimFlightLeg[Status] = 5 || DimDlightLeg[Status] <> 1 && DimFlightLeg[DepartureDate] < 06/25/2019.`

You get the error: Only a single column can be used in a true/false expression.

That's why you can't have multiple columns in an OR.

A. M. Robinson

• Edited by Monday, August 10, 2020 12:19 PM code
Monday, August 10, 2020 11:46 AM
• Hi,

Have you tried to  add brackets?

`DimFlightLeg[Status] = 1 || DimFlightLeg[Status] = 5 || (DimDlightLeg[Status] <> 1 && DimFlightLeg[DepartureDate] < 06/25/2019)`

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !

Wednesday, August 12, 2020 7:10 AM
• Parentheses don't make a difference - there are still two columns in the condition...

A. M. Robinson

Wednesday, August 12, 2020 1:21 PM
• Would it be possible if you could share a sample .sln / .pbix so we could test directly on your model?

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !

Friday, August 14, 2020 5:56 AM
• This the error:

Severity Code Description Project File Line Suppression State
Warning Measure 'Test Measures'[ASMs Flown TEST] : 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.

The model is pretty sizable, so trying to think of  way to share the model.

In the meantime, is there any way to do multiple filters that isn't iterative?

A. M. Robinson

Tuesday, August 18, 2020 2:13 PM
• It looks like a syntax error. I will try testing latter.

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !

Friday, August 21, 2020 7:58 AM
• 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 DimFlightLeg
WHERE
STATUS = 1
OR
STATUS = 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!!

A. M. Robinson

Friday, August 21, 2020 8:52 PM
• So, I finally managed to find the answer to my issue:

```ASMs Flown TEST:=
CALCULATE (
SUMX ( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength] ),
DimFlightLeg[OperatorCode] = "SY",
FILTER (
ALL ( DimFlightLeg[Status], DimFlightLeg[DepartureDate] ),
OR (
DimFlightLeg[Status] IN { 1, 5 },
AND (
DimFlightLeg[Status] = 0,
DimFlightLeg[DepartureDate] < DATE ( 2019, 6, 25 )
)
)
),
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] )
)
```

A. M. Robinson

• Marked as answer by Monday, August 24, 2020 1:01 AM
Monday, August 24, 2020 1:01 AM