Forum Discussion
Getting a measure to work with two tables
Hi Everybody,
I wondered if anybody could help with a problem I'm having writing a measure.
I have data representing the start and end times of tasks. These are datetimes with 1 minute granularity. So far I have created a measure which enables me to generate a graph of the number of concurrent tasks with one minute granularity.
PlannedDemand =
CALCULATE (COUNTROWS( 'Order Data' ),
FILTER (
'Order Data',
( [Service begin]<= MAX ( 'DemandDT'[DateTime])
&& [Service end] > MAX ( 'DemandDT'[DateTime]) )
)
)+0
The graph looks like this...
I have created a datetime dimension table (DemandDT) which I use for the x-axis in the table, and whch is referenced in the measure.
What I'm tring to do now is create a similar graph but with 1-hour buckets. Grouping the x-axis doesn't work as it just gives the last value in each hour - I would like to show the total number of minutes planned in each hour.
I wondered if there's some way to aggregate using the existing measure to sum all the visits in each hour, or whether I need to do things in a different way?
I suppose another way to do it would be to calculate how many minutes of all tasks fall withing each hour. I wrote some test code which uses the Service begin & Service end times to calculate how many minutes of a task fall within a particular hour, but I'm having difficulty building that into a measure.
Any pointers on the best approach to use would be very much appreciated.
6 Replies
- amitchandak
Super User
Anonymous , Add Hours to your datetime table and plot that on axis
Date hour = Datevalue([Datetime]) +time(Hour([Datetime]),0,0)
Change measure like
PlannedDemand =
CALCULATE ( lastnonblankvalue('DemandDT'[DateTime], COUNTROWS( 'Order Data' )),
FILTER (
'Order Data',
( [Service begin]<= MAX ( 'DemandDT'[DateTime])
&& [Service end] > MAX ( 'DemandDT'[DateTime]) )
)
)+0amitchandak thank you for your help.
I added a date column, created the measure and plotted it on a date axis as you suggested. It seems to just take the XX:59 minute from each hour, rather than summing all of the minutes in each hour.
I've attached a sample file to show:
The top chart shows is the original with one minute granularity, and the lower one is with your proposed code.
I wondered how to get the measure to sum each minute in the hour.
I also wodered whether it would be better to calculate how many minutes of each task fall within each hour, and sum those. As a proof of concept I added a calculated column to the TaskData table which does this for each row, and for a hardcoded time (08:00-09:00). I wondered if it would be possible to convert this into a measure which I could plot on an hour axis?
Many thanks
Assuming you count a task if it overlaps in any way the hour on the X axis, then you can do the comparison hour to hour:
PlannedDemandHtoH = CALCULATE( COUNTROWS( TaskData ), DATEVALUE (TaskData[Task begin]) + TIME (HOUR (TaskData[Task begin]), 0, 0) <= MAX ( DemandDT[Date hour] ), DATEVALUE (TaskData[Task end]) + TIME (HOUR (TaskData[Task end]), 0, 0) >= MAX (DemandDT[Date hour] ) )Or if you want to be more explicit with variables:
PlannedDemandHtoH = CALCULATE( COUNTROWS( TaskData ), FILTER ( TaskData, VAR Hour_Begin = DATEVALUE (TaskData[Task begin]) + TIME (HOUR (TaskData[Task begin]), 0, 0) VAR Hour_End = DATEVALUE (TaskData[Task end]) + TIME (HOUR (TaskData[Task end]), 0, 0) RETURN Hour_Begin <= MAX ( DemandDT[Date hour] ) && Hour_End >= MAX (DemandDT[Date hour] ) ) )I tested by hand a few examples and the result is according to my interpretation. If you want to allocate tasks by hour based on the proportion within the hour or some custom logic you'll have to adjust the measure.
You could also just add two more calculated columns (Hour Begin and Hour End) to your Fact and use them in the measure instead of computing the Date-Hour variables/values within the measure.
Hi,
Thank you for for reply. I was trying to count the number of minutes allocated in each hour, so I think I would need to do someting other than count rows.
I added a calculated column (code below) to the TaskData table which calculates the number of minutes of each task which fall beweem 08:00-09:00. This just uses a fixed value for the hour, and I wondered how to convert this into a measure to be able to plot on a graph with the hour as the x-axis.
HourlyLoad08 = VAR _StartOffset = MIN(MAX(DATEDIFF("17/01/2022 08:00:00",'TaskData'[Task begin],MINUTE),0),60) VAR _EndOffset = MIN(Max(DATEDIFF("17/01/2022 08:00:00",'TaskData'[Task end],MINUTE),0),60) RETURN _EndOffset - _StartOffsetI think your problem is not a DAX problem but an ETL one. Your input data is not appropriate for the kind of computation that you want to achieve and forcing it through DAX unecessarily over-complicates it. I would transform the input table as a cross join between a one column table (list) that spans all the hours between minimum and maximum with your TaskData table and then fill in each hour's row the number of minutes within this span. Then you remove all the NULL rows (your cross joined table will be very sparse). Or maybe you can dynamically split it only in the relevant number of rows. Once the input table has this structure, the DAX would be trivial. I will try to build that in SQL to show what I mean, the same must be achievable in Power Query.
