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.
Hi, I am in need of writing a measure, but I am unsure how I should write it.
Table Name: Cal3
Columns [Time] and [Time_Passed]
Basically I am wanting to write an if statement that follows: IF the maximum time passed between 11:00am and 12:00pm is greater than 20, then return "Non Continuous" if true or return "Continuous" if false.
Below is a screenshot of my data. In this case, the measure should return "NonContinuous", since the maximum value between 11am and 12pm is 50.133.
Solved! Go to Solution.
@Josh97Ellis this one should be working then...
TimeCont2 =
VAR T =
(
IF (
AND (
MINA ( Cal3[Time] ) > TIMEVALUE ( "10:59" ),
MAXA ( Cal3[Time] ) <= TIMEVALUE ( "12:00" )
),
"YES",
"NO"
)
)
VAR E =
SUM ( Cal3[Time_Passed ] ) > 20
RETURN
IF ( AND ( T = "YES", E = TRUE ), "Non Continuous", "Continuous" )
Can you show me some examples where it isn't working?
Proud to be a Super User!
How about this?
TimeCont =
VAR T =
(
IF (
AND ( Cal3[Time] > TIMEVALUE ( "10:59" ), Cal3[Time] <= TIMEVALUE ( "12:00" ) ),
"YES",
"NO"
)
)
RETURN
IF (
AND ( T = "YES", Cal3[Time_Passed ] > 20 ),
"Non Continuous",
"Continuous"
)
Proud to be a Super User!
Thanks for the reply. That does not seem to work. The column names cannot be recognized. I believe that since it is using an IF function, you must use an aggregate functions or a measure for the column.
Hmm @Josh97Ellis it seems to be working fine when I built it out as a calculated column...
Proud to be a Super User!
I see..
that works as a calculated column, however, It would be more useful for me to have this as a measure, as the output of this measure will be used to determine other measures.
@Josh97Ellis how about this?
TimeCont2 =
VAR T =
(
IF (
AND (
MIN ( Cal3[Time] ) > TIMEVALUE ( "10:59" ),
MAX ( Cal3[Time] ) <= TIMEVALUE ( "12:00" )
),
"YES",
"NO"
)
)
RETURN
IF (
AND ( T = "YES", SUM ( Cal3[Time_Passed ] ) > 20 ),
"Non Continuous",
"Continuous"
)
Proud to be a Super User!
That is close. It does not seem to be providing me with the right result.
For instance, I know that it shoud provide noncontinuous, since there is time_passed value of 50 minutes within that time interval.
I noticed you used SUM instead of MAX in the return section. Could this be why?
My bad @Josh97Ellis I didn't read clearly that it can't be greater than 50.133
TimeCont3 =
VAR T =
(
IF (
AND (
MINA ( Cal3[Time] ) > TIMEVALUE ( "10:59" ),
MAXA ( Cal3[Time] ) <= TIMEVALUE ( "12:00" )
),
"YES",
"NO"
)
)
VAR E =
SUM ( Cal3[Time_Passed ] ) > 20
VAR F =
SUM ( Cal3[Time_Passed ] ) < 50.1334
RETURN
IF ( AND ( T = "YES", AND ( E, F ) = TRUE ), "Non Continuous", "Continuous" )
Proud to be a Super User!
I apologize if it isn't clear.
The value can be greater than 50.133. The value can be anything, the 50.133 was just a value on that day.
If there are any values in the time_passed column that are greater than 20 between and is 11:00am and 12:00pm, then return "Noncontinuous". However, if there are no values that are greater than 20, then return "Continuous"
@Josh97Ellis this one should be working then...
TimeCont2 =
VAR T =
(
IF (
AND (
MINA ( Cal3[Time] ) > TIMEVALUE ( "10:59" ),
MAXA ( Cal3[Time] ) <= TIMEVALUE ( "12:00" )
),
"YES",
"NO"
)
)
VAR E =
SUM ( Cal3[Time_Passed ] ) > 20
RETURN
IF ( AND ( T = "YES", E = TRUE ), "Non Continuous", "Continuous" )
Can you show me some examples where it isn't working?
Proud to be a Super User!
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |