Choose values based on time range.

[Anonymous]

hello all, 

i'm facing an issue choosing values based on time range.

here's the situation i have an intial student plannings and teachers planning,knowing that each student is dispatched to a specific teacher,so i want to automate this process, so the query can check if the student is present during the presence of the teacher and selceted the names of student if the condition is true.

EX

Student A: start at 08:00 am and finishes at 12 pm, 

teacher of student A is present at 08:00-08:30.

Any help from your part will be highly apperciated.

[mahoneypat]

Hard to give a specific expression without sample data or seeing your model, but basically you want an expression that checks that two conditions are met:

the max teacher time is >= min student time && (and) min teacher time is <= max student time.

Pat

[Anonymous]

Yes indeed it's hard,but based on your suggestion I want the query to pick the names if both conditions are met,

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKwsDIwACIg09AIzkQSBTKNwcxYnWglJ5CAJUKHAVYdljAdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, #"Stu start" = _t, #"Stu end" = _t, #"Tea start" = _t, #"Tea end" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Stu start", type time}, {"Stu end", type time}, {"Tea start", type time}, {"Tea end", type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ( [Stu start] <= [Tea start] and [Stu end] >= [Tea end])),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Student"})
in
    #"Removed Other Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous I agree with @mahoneypat basically you are looking for LookupValue Range: LOOKUPVALUE Range - Microsoft Power BI Community