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.
Hi,
I need to create a weekly report on emails received over the previous week between 19:00 Friday and 18:59 the following Friday.
My visual needs to display the figures for the following:
Friday (19:00-23:59)
Saturday (00:00-23:59)
Sunday (00:00-23:59)
Monday (00:00-23:59)
Tuesday (00:00-23:59)
Wednesday (00:00-23:59)
Thursday (00:00-23:59)
Friday (00:00-18:59)
Total received
Here is an example of the query data from the mailbox, with the rows highlighted red being the ones I need the filter to pick up:
I can't seem to find a way to use the slicer to filter on both date and time.
I wondered if I could create a date/time table (I know not recommended) that contained the hour of each day, starting at 19:00 Friday and finishing at 18:00 the following Friday, but unsure how to do this and if it would be too memory intensive.
Any suggestions on how else I can achieve this?
Solved! Go to Solution.
Hi @ktbell -
Have you tried putting a calculated column on your data, putting each row into a "group" of sorts?
Something like this:
DisplayGroup =
VAR __myDOW =
WEEKDAY ( FridayGroups[DT], 1 )
VAR __myTime =
HOUR ( FridayGroups[DT] )
RETURN
SWITCH (
TRUE (),
AND ( __myDOW = 6, __myTime >= 19 ), "Friday (19:00-23:59)",
__myDOW = 6, "Friday (0:00-18:59)",
CONCATENATE ( FORMAT ( FridayGroups[DT], "dddd" ), " (0:00-23:59)" )
)
Additionally/alternatively, you create a date group dimension table with an ID and group name (1, "Friday (19:00-23:00)", etc), then use the calculated column to just return 1-8 and make a relationship between the group IDs. That would go something like this:
DisplayGroup =
VAR __myDOW =
WEEKDAY ( FridayGroups[DT], 15 ) //15 has Friday = 1, Thurs = 7
VAR __myTime =
HOUR ( FridayGroups[DT] )
RETURN
SWITCH (
TRUE (),
AND ( __myDOW = 1, __myTime >= 19 ), 1,
__myDOW = 1, 8,
__myDOW
)
Hope this helps
David
Hi @ktbell -
Have you tried putting a calculated column on your data, putting each row into a "group" of sorts?
Something like this:
DisplayGroup =
VAR __myDOW =
WEEKDAY ( FridayGroups[DT], 1 )
VAR __myTime =
HOUR ( FridayGroups[DT] )
RETURN
SWITCH (
TRUE (),
AND ( __myDOW = 6, __myTime >= 19 ), "Friday (19:00-23:59)",
__myDOW = 6, "Friday (0:00-18:59)",
CONCATENATE ( FORMAT ( FridayGroups[DT], "dddd" ), " (0:00-23:59)" )
)
Additionally/alternatively, you create a date group dimension table with an ID and group name (1, "Friday (19:00-23:00)", etc), then use the calculated column to just return 1-8 and make a relationship between the group IDs. That would go something like this:
DisplayGroup =
VAR __myDOW =
WEEKDAY ( FridayGroups[DT], 15 ) //15 has Friday = 1, Thurs = 7
VAR __myTime =
HOUR ( FridayGroups[DT] )
RETURN
SWITCH (
TRUE (),
AND ( __myDOW = 1, __myTime >= 19 ), 1,
__myDOW = 1, 8,
__myDOW
)
Hope this helps
David
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |