locked
Complex Filter In Measure RRS feed

  • 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

Answers

  • 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 ansonee 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 !
    For more information, please refer to the sticky post.

    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 ansonee 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 !
    For more information, please refer to the sticky post.

    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 !
    For more information, please refer to the sticky post.

    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 !
    For more information, please refer to the sticky post.

    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 ansonee Monday, August 24, 2020 1:01 AM
    Monday, August 24, 2020 1:01 AM