Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Time Intelligence Table - Minutes Over 60

I created a Time Intelligence Table (just like a Calendar Table but for time). The goal is to create a reference calculated column which will be broken into 15 min increments ( <=15mins / 16-30 / 31-45 / 46-60 / Over 1hr ). Current problem is that Time Intelligence Table currently has minutes that stop at 59. My data table has minutes that have 60+mins. The result is that any results Over 1hr will be bucketed into categories that is not correct (see screenshot below).

NOT CORRECTNOT CORRECT 

I found a work-around solution by creating the calculated 15 min increment column in the data table. My hope was to have it in the Time Intelligence Table and have it work across multiple different data tables. 

CORRECT - TARGET OUTCOMECORRECT - TARGET OUTCOME

 

Any ideas on how I can solve this? 

 

Below is the syntax for the 15min increments:

15min Time Band =
SWITCH(
TRUE(),
'Data Table'[TIMEDIFF.MINUTE] >= 0 && 'Data Table'[TIMEDIFF.MINUTE] <= 15, "15 mins or Less",
'Data Table'[TIMEDIFF.MINUTE] >= 16 && 'Data Table'[TIMEDIFF.MINUTE] <= 30, "16 - 30 mins",
'Data Table'[TIMEDIFF.MINUTE] >= 31 && 'Data Table'[TIMEDIFF.MINUTE] <= 45, "31 - 45 mins",
'Data Table'[TIMEDIFF.MINUTE] >= 46 && 'Data Table'[TIMEDIFF.MINUTE] <= 60, "46 - 60 mins",
'Data Table'[TIMEDIFF.MINUTE] >= 61, "Over 1hr"
)
 
Below is the syntax for the Time Intelligence Table: 
 
Time =
VAR _series =
GENERATESERIES( 1, 1440, 1 )
VAR _time =
ADDCOLUMNS( _series, "TimeandDay", TIME( 0, [Value], 0 ) )
RETURN
ADDCOLUMNS(
_time,
"TimeKey", FORMAT( [TimeandDay], "hhmm" ),
"Actual Time", FORMAT( [TimeandDay], "HH:MM AM/PM" ),
"HourNum", HOUR( [TimeandDay] ),
"Hour", FORMAT( [TimeandDay], "HH:00" ),
"Hour Extended", FORMAT( [TimeandDay], "HH:00 AM/PM" ),
"Hour Short AMPM", FORMAT( [TimeandDay], "H AM/PM" ),
"Minute", MINUTE( [TimeandDay] ),
"AMPM", FORMAT( [TimeandDay], "AMPM" ),
"1HR TimeBand", FORMAT( [TimeandDay], "HAM/PM" ) & "-" & FORMAT( [TimeandDay] + TIME( 1, 0, 0 ), "HAM/PM" )
)
1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

So I'm curious.  Your 'workaround' would be the way I would accomplish this task.

 

If you've got a time dimension table, that's just like listing out all the possibilities of a clock in various formats.  It seems like you're trying to assign each minute of the day a category like "Over 1hr".  How can 1:15PM be "31-45 mins"?

 

It makes much more sense to assign that category to your data table.   Your table seems to have a Start Time and End Time, and a TIMEDIFF.MINUTE that counts the minutes.  You compare the TIMEDIFF.MINUTE against your buckets for each row, and assign it a category like "15 mins or less" becasue a specific entry can have that property.

 

Why not just apply the same DAX to each field on each table to give it a category?  It's just copy/pasting and changing the field that's being tested against.

View solution in original post

4 REPLIES 4
Cmcmahan
Resident Rockstar
Resident Rockstar

So I'm curious.  Your 'workaround' would be the way I would accomplish this task.

 

If you've got a time dimension table, that's just like listing out all the possibilities of a clock in various formats.  It seems like you're trying to assign each minute of the day a category like "Over 1hr".  How can 1:15PM be "31-45 mins"?

 

It makes much more sense to assign that category to your data table.   Your table seems to have a Start Time and End Time, and a TIMEDIFF.MINUTE that counts the minutes.  You compare the TIMEDIFF.MINUTE against your buckets for each row, and assign it a category like "15 mins or less" becasue a specific entry can have that property.

 

Why not just apply the same DAX to each field on each table to give it a category?  It's just copy/pasting and changing the field that's being tested against.

Anonymous
Not applicable

I guess I was over thinking it. My goal was to have it housed in the Time table so I can apply it across different tables. It is still possible to accomplish what I am hoping to do but I am pretty sure I would have to create a separate minute key or something in that nature. 

 

Thanks!

I guess it just doesn't make sense to me how adding these values to your time dimension is helpful. If I wrote out all the possible times in a day, how would it make sense to say that 4:35p has the attribute "15 mins or less" and 4:36 is "16-30 mins" for however you would categorize each minute? 

 

What's happening when you try to add the categories to your time table but then don't have any counted in the 1 hour+ bucket is that you've checked the minute of each timestamp and given it a corresponding category.  Then you take a duration (let's say 69 minutes) and returning the bucket for minute 69 of the day, which would be 1:09a, which has a category of "15 mins or less". 

 

If this was working like you thought, how would "applying this to multiple tables" work?  Would you want something like "Return all rows where the Time is 4:35p AND are in the "Less than 15min" bucket? Or some sort of filter that can be easily applied to different durations by using USERELATIONSHIP in queries? 

 

I'm more than happy to help you work out a proper data structure for this, but treating timestamps and durations as the same type of data just isn't useful.

otravers
Community Champion
Community Champion

@Anonymous I'm sure there's a DAX solution, but I tend to solve this type of issue either at the source (e.g. SQL view) or in Power Query. For one, it's likely to perform better. Have you considered creating a column for your minute banding in PQ?

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.