Putting times into 4 different categorizes

[benjaminhoang]

Hello,

I am 2 collums with times the first time when someone enters and second time when someone leaves. I want to put third collumn that seperates the times into 4 categories for example 24-6, 6-12, 12-18, 18-24.

[v-jiascu-msft]

Hi @benjaminhoang,

Could you please mark the proper answers as solutions?

Best Regards,

[v-jiascu-msft · Accepted solution]

Hi @benjaminhoang,

There are two time columns. Which one would like to calculate? Maybe this one?

Column =
SWITCH (
    TRUE (),
    [Entry Time] >= TIME ( 0, 0, 0 )
        && [Entry Time] <= TIME ( 6, 0, 0 ), "24-6",
    [Entry Time] > TIME ( 6, 0, 0 )
        && [Entry Time] <= TIME ( 12, 0, 0 ), "6-12",
    [Entry Time] > TIME ( 12, 0, 0 )
        && [Entry Time] <= TIME ( 18, 0, 0 ), "12-18",
    [Entry Time] > TIME ( 18, 0, 0 )
        && [Entry Time] <= TIME ( 23, 59, 59 ), "18-24",
    "error"
)

Best Regards,

[benjaminhoang]

This looks like it is working but can you pelase break down the formula for me?

[benjaminhoang]

[Greg_Deckler]

Sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, you should be able to do this with a SWITCH(TRUE()...) statement.

[benjaminhoang]

I am novice what would that swithc(true()....) look like?

[Greg_Deckler]

Not 100% certain of the requirements but it would look something like:

Measure =
VAR __hour = HOUR([Entry Time])
VAR __minute = MINUTE([Entry Time])
RETURN
SWITCH(TRUE(),
  __hour = 24 || __hour < 6 || (__hour = 6 && __minute = 0),"24-6",
  (__hour = 6 && __minute > 0) || __hour < 12 || (__hour = 12 && __minute = 0),"6-12",
etc.
)