Help with writing an IF measure

[Anonymous]

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.

[Watsky]

@Anonymous 

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

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[Anonymous]

@Watsky 

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. 

[Watsky]

Hmm @Anonymous it seems to be working fine when I built it out as a calculated column...

[Anonymous]

@Watsky 

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. 

[Watsky]

@Anonymous  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"
    )

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[Anonymous]

@Watsky 

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?

[Watsky]

My bad @Anonymous  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" )

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[Anonymous]

@Watsky 

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"

[Watsky · Accepted solution]

@Anonymous 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?