Forum Discussion

blfox33's avatar
blfox33
New Member
3 years ago
Solved

Help Please with an IF Statement with Time Values to create Interval Groups

Hi all,

 

My apologies in advance as I haven't ever asked a question using this community before but wanted to say "thank you" to the community because this support group has helped me out several times before 🙂 

 

I have been working on a way to create a new column in Power BI for a little over a week now and I can't seem to grasp what I need to do. This is a side project that I am trying to help a coworker with so I haven't been able to put too much time into it but have reached the point where I am out of ideas.

 

My goal is to take a column that has previously been converted from a 'Date/Time" field into just a Time column. Now, I need to take that time column and create a new column that looks at the time and then based on the conditions below, returns the time slots back to me. It is easier to show below:

 

Column Name with just Time in it:  Helper 

Time Slot Conditions:

If [Helper] >= 07:30 to 10:30, "Breakfast"

If [Helper] >= 12:00 to 15:30, "Lunch"

If [Helper] >= 16:30 to 18:30, "Snacks"

If [Helper] >= 19:30 to 23:00, "Dinner"

If [Helper] >= 12:00 to 04:00, "Midnight"

Else "Other Snacks"

 

I will admit that I have tried all kinds of formulas but none of them seem to work and give error messages. Can anyone please help because I am at a loss.


Thank you!

 

 

 

 

 

  • jgeddes's avatar
    jgeddes
    3 years ago

    This seems like a good case to use a SWITCH statement in a calculated column.

    Something like this should get you pointed in the right direction. (I used [Time] as the column with times in it where you had [Helper].)

     

    _condition =
    SWITCH(
        TRUE(),
        AND([Time] >= TIME(7,30,0), [Time] <= TIME(10,30,0)), "Breakfast",
        AND([Time] >= TIME(12,0,0), [Time] <= TIME(15,30,0)), "Lunch",
        AND([Time] >= TIME(16,30,0), [Time] <= TIME(18,30,0)), "Snacks",
        AND([Time] >= TIME(19,30,0), [Time] <= TIME(23,0,0)), "Dinner",
        AND([Time] >= TIME(0,0,0), [Time] <= TIME(4,0,0)), "Midnight",
        "Other Snacks"
    )

3 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    3 years ago

    This seems like a good case to use a SWITCH statement in a calculated column.

    Something like this should get you pointed in the right direction. (I used [Time] as the column with times in it where you had [Helper].)

     

    _condition =
    SWITCH(
        TRUE(),
        AND([Time] >= TIME(7,30,0), [Time] <= TIME(10,30,0)), "Breakfast",
        AND([Time] >= TIME(12,0,0), [Time] <= TIME(15,30,0)), "Lunch",
        AND([Time] >= TIME(16,30,0), [Time] <= TIME(18,30,0)), "Snacks",
        AND([Time] >= TIME(19,30,0), [Time] <= TIME(23,0,0)), "Dinner",
        AND([Time] >= TIME(0,0,0), [Time] <= TIME(4,0,0)), "Midnight",
        "Other Snacks"
    )
    • blfox33's avatar
      blfox33
      New Member
      3 years ago

      Hello! Oh my goodness, it is working now!!! Thank you so much!

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    blfox33 Time values are fractions of a day, like 1/24/60 would be in minutes so maybe something like:

    Time Slot Conditions Column:
VAR __Minutes = 1/24/60
VAR __MinutesInHour = 60 * __Minutes
VAR __Helper = [Helper]
VAR __Resut = 
  SWITCH( TRUE(),
    [Helper] >= __MinutesInHour * 7.5 && <= __MinutesInHour * 10.5, "Breakfast",
    [Helper] >= __MinutesInHour * 16.5 && <= __MinutesInHour * 18.5, "Snacks",
    [Helper] >= __MinutesInHour * 19.5 && <= __MinutesInHour * 23, "Dinner",
    [Helper] >= __MinutesInHour * 0 && <= __MinutesInHour * 4, "Midnight",
    "Other Snacks"
  )
RETURN
  __Result