Calculating Event duration Hours by Resource and Date

[wkeicher]

I have a Table of events. Each Event has a date & duration( in hours). Each Event has a resource assigned through a relationship with an assignment table. . I am trying to build a visual of hours by resource and date.

My result is the same value for each resource by date. Also the date field (by way of Hierarchy) only shows the Number not the actual date.

[parry2k · Accepted solution]

@wkeicher issue is with resource table on many side. Either change the cross filter direction of relationship to both or write a measure use CROSSFILTER function in it.

[Anonymous]

when its a repeating value like that it more often than not a relationship problem where the filters are not flowing as you would expect. Any chance you could share the pbix file?

[parry2k]

@wkeicher can you share tables relationship diagram and also what you are using in matrix for rows, columns, and value.

[wkeicher]

I highligted the Joined fields.

[wkeicher]

Rows = The Resource Name

Coumns = Dates

Hours = Hours assigned to the event

Ideally - Once I get the correct hours, I will want to measure the hours per resouce/Hours in a work day to get Utilization %.

[parry2k · Accepted solution]

@wkeicher issue is with resource table on many side. Either change the cross filter direction of relationship to both or write a measure use CROSSFILTER function in it.

[wkeicher]

@Anonymous  - So similiar to a different thread, I am now trying to provide a forecasted utilization% for upcoming events. With the help of the folks on this thread I am reporting the correct hours that are booked for future events. My next challenge is to calcuate utilization for each resource and the grouo they belong to.

I attempted to use the same column with modification, but getting much lower than expected results.

ForecastedUtil =

var workday_available_hours = COUNTROWS(FILTER('Working Dates','Working Dates'[IsWorkday] = "Yes")) * 7.5

var workday_booked_hours = CALCULATE(SUM('Events'[Hours]), 'Working Dates'[IsWorkday] = "Yes")

var non_workday_booked_hours = CALCULATE(SUM('Events'[Hours]), 'Working Dates'[IsWorkday] = "No")

return DIVIDE(

workday_booked_hours + non_workday_booked_hours,

workday_available_hours + non_workday_booked_hours

)

The sample below is to show one day (the 23rd)

[Anonymous]

@wkeicher - First step in troubleshooting is to create separate test measures for each variable to see where it is going wrong. Then we can analyze what is happening with that component.

Cheers,

Nathan

[wkeicher]

@Anonymous Very Helpful. So it looks like my caclulation for Available hours is incorrect. I tried both and single in the relationship no change.

[parry2k]

@wkeicher you should just look at what countrows return before calculation to make sure you are getting correct count. Also it depends what  is the filter context for the working date table? Are you filtering the data or what else is in matrix?

Just looking at dax expression doesn't help to resolve the issue, you have to provide the full context on how you are using it.

[Anonymous · Accepted solution]

@wkeicher - At issue here is that the Date table is not related to the fact table, so the calculation is counting all dates in the date table. As a best practice, you generally want to make relationships from your date dimension table to each fact table.

Hope this helps,

Nathan

[wkeicher]

The Relationship was there, however the data types were mismatched Date verssus Date/Time. Thank You!

[wkeicher]

@Anonymous Very Helpful. So it looks like my caclulation for Available hours is incorrect. I tried both and single in the relationship no change.

[parry2k]

@wkeicher just to confirm your original issue is resolved, correct?

[wkeicher]

yes - the hours are calculating correctly now - Thank You. The next step is to calculate Utilization %

[parry2k]

@wkeicher did you use cross filter direction to "Both" to fixed the issue. Make sure accept the resolution so that others can take advantage of it.

Regarding 2nd question, make sure your each var calculation is returning correct value, it will help to debug where the issue is.