Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
IF(
(WEEKDAY(df_Case[CreatedDate]) >= 6 && WEEKDAY(df_Case[CreatedDate]) <= 2) && (HOUR(df_Case[CreatedDateTime]) >= 18 && HOUR(df_Case[CreatedDateTime]) <= 7),
"Pager",
IF(
(WEEKDAY(df_Case[CreatedDate]) >= 5 && WEEKDAY(df_Case[CreatedDate]) <= 1) && (HOUR(df_Case[CreatedDateTime]) >= 7 && HOUR(df_Case[CreatedDateTime]) <= 20),
"Pager",
IF(
(WEEKDAY(df_Case[CreatedDate]) >= 6 && WEEKDAY(df_Case[CreatedDate]) <= 2) && (HOUR(df_Case[CreatedDateTime]) >= 3 && HOUR(df_Case[CreatedDateTime]) <= 16),
"Pager",
"Other"
)
)
Thank you so much
Solved! Go to Solution.
Let's assume business hours are from 8 AM to 6 PM. Therefore, we are looking at tickets opened after 6 PM on Friday until before 8 AM on Monday. The updated DAX code should look something like this:
IF(
// Check if the day is Friday and time is after 6 PM
(WEEKDAY(df_Case[CreatedDate]) = 6 && HOUR(df_Case[CreatedDateTime]) >= 18) ||
// Check if the day is Saturday or Sunday (whole day)
(WEEKDAY(df_Case[CreatedDate]) = 1 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
// Check if the day is Monday and time is before 8 AM
(WEEKDAY(df_Case[CreatedDate]) = 2 && HOUR(df_Case[CreatedDateTime]) < 8),
"Pager",
"Other"
)
Proud to be a Super User!
Thank you so much for the help. I can combine above formula like below right
Test =
IF (
df_Case[EndAccount.Region] = "NA",
IF (
// Check if the day is Friday and time is after 6 PM
(
WEEKDAY ( df_Case[CreatedDate] ) = 6
&& HOUR ( df_Case[CreatedDateTime] ) >= 18
)
|| // Check if the day is Saturday or Sunday (whole day)
(
WEEKDAY ( df_Case[CreatedDate] ) = 1
|| WEEKDAY ( df_Case[CreatedDate] ) = 7
)
|| // Check if the day is Monday and time is before 8 AM
(
WEEKDAY ( df_Case[CreatedDate] ) = 2
&& HOUR ( df_Case[CreatedDateTime] ) < 8
),
"Pager",
"Other"
),
IF (
df_Case[EndAccount.Region] = "IMETA",
IF (
// Check if the day is Thursday and time is after 7 PM
(
WEEKDAY ( df_Case[CreatedDate] ) = 5
&& HOUR ( df_Case[CreatedDateTime] ) >= 19
)
|| // Check if the day is Friday or Saturday (whole day)
(
WEEKDAY ( df_Case[CreatedDate] ) = 6
|| WEEKDAY ( df_Case[CreatedDate] ) = 7
)
|| // Check if the day is Sunday and time is before 8 AM (20 in your time)
(
WEEKDAY ( df_Case[CreatedDate] ) = 1
&& HOUR ( df_Case[CreatedDateTime] ) < 20
),
"Pager",
"Other"
),
""
)
)
To adjust your formula for a different weekend schedule, such as Thursday to Saturday, as in some Middle Eastern countries, you need to modify the WEEKDAY() function values to align with the new weekend days. In your case, you want to adjust the logic for Thursday post 7 PM, Friday and Saturday the whole day, and Sunday before 8 AM.
Here's how the adjusted formula would look:
IF(
// Check if the day is Thursday and time is after 7 PM
(WEEKDAY(df_Case[CreatedDate]) = 5 && HOUR(df_Case[CreatedDateTime]) >= 19) ||
// Check if the day is Friday or Saturday (whole day)
(WEEKDAY(df_Case[CreatedDate]) = 6 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
// Check if the day is Sunday and time is before 8 AM (20 in your time)
(WEEKDAY(df_Case[CreatedDate]) = 1 && HOUR(df_Case[CreatedDateTime]) < 20),
"Pager",
"Other"
)
Proud to be a Super User!
Let's assume business hours are from 8 AM to 6 PM. Therefore, we are looking at tickets opened after 6 PM on Friday until before 8 AM on Monday. The updated DAX code should look something like this:
IF(
// Check if the day is Friday and time is after 6 PM
(WEEKDAY(df_Case[CreatedDate]) = 6 && HOUR(df_Case[CreatedDateTime]) >= 18) ||
// Check if the day is Saturday or Sunday (whole day)
(WEEKDAY(df_Case[CreatedDate]) = 1 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
// Check if the day is Monday and time is before 8 AM
(WEEKDAY(df_Case[CreatedDate]) = 2 && HOUR(df_Case[CreatedDateTime]) < 8),
"Pager",
"Other"
)
Proud to be a Super User!
@amustafa
Thank you so much for the quick response. This is great, one last question, If I have a different time like the Middle East, for example, Thursday to Sunday, I have to repost all the steps again right? Is there an efficient way
Assume their Weekend start on Thursday (5) and Time after (7 - this is covert to my time from their time) Friday and Saturday, before Sunday (1) 7 AM (20 - convert to my time)
IF(
// Check if the day is Friday and time is after 6 PM
(WEEKDAY(df_Case[CreatedDate]) = 6 && HOUR(df_Case[CreatedDateTime]) >= 18) ||
// Check if the day is Saturday or Sunday (whole day)
(WEEKDAY(df_Case[CreatedDate]) = 1 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
// Check if the day is Monday and time is before 8 AM
(WEEKDAY(df_Case[CreatedDate]) = 2 && HOUR(df_Case[CreatedDateTime]) < 8),
"Pager",
IF(
(WEEKDAY(df_Case[CreatedDate]) = 5 && HOUR(df_Case[CreatedDateTime]) >= 7) ||
(WEEKDAY(df_Case[CreatedDate]) = 6 || WEEKDAY(df_Case[CreatedDate]) = 7) ||
(WEEKDAY(df_Case[CreatedDate]) = 1 && HOUR(df_Case[CreatedDateTime]) < 20),
"Pager",
"Other"
))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
96 | |
84 | |
70 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |