Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
)
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
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you!!
This is EXACTLY what I was looking for!
Awesome1
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |