Hi everyone, I’m trying to simply group values by the time of day to assign a ‘group name’
For example
If the Time is between 9:30 am and 12:30 pm then “Morning”
If the Time is between 12:30 pm and 3:30 pm then “Afternoon”
If Time is between 3:30pm and 6:30pm “Late Afternoon”
If the Time is between 6:30 pm and 9:30 pm then “Evening”
I’m trying this in a custom column, but I keep getting a blank column so I’m unsure of the simplest way to approach this.
This is the DAX formula I used
Time Groups =
SWITCH( TRUE(),
Reactions1[TIME] > TIMEVALUE(“9:00 PM”) && Reactions1[TIME] < TIMEVALUE(“11:59 AM”), “Night”,
Reactions1[TIME] > TIMEVALUE(“6:00 PM”) && Reactions1[TIME] < TIMEVALUE(“9:00 PM”), “Evening”,
Reactions1[TIME] > TIMEVALUE(“4:00 PM”) && Reactions1[TIME] < TIMEVALUE(“6:00 PM”), “Late Afternoon”,
Reactions1[TIME] > TIMEVALUE(“12:00 PM”) && Reactions1[TIME] < TIMEVALUE(“4:00 PM”), “Afternoon”,
Reactions1[TIME] > TIMEVALUE(“7:00 AM”) && Reactions1[TIME] < TIMEVALUE(“12:00 PM”), “Morning”,
Reactions1[TIME] > TIMEVALUE(“4:00 AM”) && Reactions1[TIME] < TIMEVALUE(“7:00 AM”), “Dawn”,
Reactions1[TIME] < TIMEVALUE(“4:00 AM”), “Mid night”,
BLANK() )
Thanks in advance if you can lend some advice!
PROJECT.pbix (647.7 KB)
Reactions.xlsx (1.0 MB)
Hello @Wazenge, please upload your work in progress Power BI desktop file, as well as your Excel Data file so that members of the Enterprise DNA forum may assist you.
If your measure is working as intended, and you want to use DAX to create the column then theres not a lot wrong with your choice, the only things I would point out would be it would be best to use a variable to store the Reactions1[TIME]
VAR ReactionsTime = …
RETURN
Then use that in your formula, and I would use an And funtion, as there only two tests, if your going to be testing an upper and lower limit one of those needs to be <= or >= to include the whole range so it depends how you want to split it, but in this case as the last time is 11:59 the upper limit would probably need the equals. And I think that should be PM?
As it is caclulated until something is true you could technically have the most popular times first, it may speed up performance, otherwise you could run the operations in order of time so its logiacal to read.
SWITCH(TRUE(), AND(ReactionsTime >= TIMEVALUE(9PM), ReactionsTime < TIMEVALUE(11:59PM))
I havent typed the exact formula its more for demonstration of how would approach a similar calculation.
1 Like
Thank you all for your help. I figured it out, I referenced the table column itself. In case someone else may have such a problem in the future here is what I used.
Time Groups =
SWITCH(
TRUE(),
AND(
HOUR(Reactions1[TIME]) >=7,
HOUR(Reactions1[TIME]) < 12), “Morning”,
AND(
HOUR(Reactions1[TIME]) >= 12,
HOUR(Reactions1[TIME]) < 16 ), “Afternoon”,
AND(
HOUR(Reactions1[TIME]) >= 16,
HOUR(Reactions1[TIME]) < 18 ), “Late Afternoon”,
AND(
HOUR(Reactions1[TIME]) >= 18,
HOUR(Reactions1[TIME]) < 20 ), “Evening”,
AND(
HOUR(Reactions1[TIME]) >= 20,
HOUR(Reactions1[TIME]) < 24 ), “Night”,
AND(
HOUR(Reactions1[TIME]) >= 00,
HOUR(Reactions1[TIME]) < 4 ), “Mid Night”,
AND(
HOUR(Reactions1[TIME]) >= 4,
HOUR(Reactions1[TIME]) < 7 ), “Dawn”,
BLANK()
)
1 Like
