Forum Discussion
Help with Dynamic DAX Measure
Imagine a data set where there are multiple work dates, multiple employees on each work date, and multiple tasks performed by each employee on each work date.
How can I create a DYNAMIC measure that calculates the number of work hours depending upon how many dates are selected?
Example: if 3 dates are selected from the date slicer, then
Number of work hours = [number of days selected] x [8 hours per day] x [number of employees in the dataset]
This measure will serve as the denominator for my Productivity measure which =
[Total Productivity Time] / [Number of work hours]
Thanks.
Hi, I am so sorry I must have missed your last reply/solution.
At the end, this is what I used:
Total Work Mins = [Total Number of Employees]*480 (=8x60=No. of work mins in a day)*[Number of Weekdays Selected]
where
Number of Weekdays Selected = var MinDate = MIN(Date_Calendar[Date])
var MaxDate = MAX(Date_Calendar[Date])
return CALCULATE(DISTINCTCOUNT(Date_Calendar[Date]),'Date_Calendar'[Date]<MaxDate,'Date_Calendar'[Date]>=MinDate,Date_Calendar[IsWeekday]=1)
4 Replies
We would need to see some data, but Power BI, by default, will filter all measures down to what you have selected. You don't need different measures for different filter conditions. They are always, by definition, dynamic. What gets tricky is telling a measure to ignore filters. The denominator of a ratio, for example, would need to do that. Still dynamic, but you are overriding certain aspects of it.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI ForumAnonymous , Date is either selected from the date table or you fact So the number of the day will be
Days = count(Date[Date])
Days = distinctcount(Table[Date])
If you need working days. Make sure you have date table with Working days Marked as 1
Day = Sum(Date[Working Day])
Refer this file for working days: https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
Hi, I am so sorry I must have missed your last reply/solution.
At the end, this is what I used:
Total Work Mins = [Total Number of Employees]*480 (=8x60=No. of work mins in a day)*[Number of Weekdays Selected]
where
Number of Weekdays Selected = var MinDate = MIN(Date_Calendar[Date])
var MaxDate = MAX(Date_Calendar[Date])
return CALCULATE(DISTINCTCOUNT(Date_Calendar[Date]),'Date_Calendar'[Date]<MaxDate,'Date_Calendar'[Date]>=MinDate,Date_Calendar[IsWeekday]=1)
Hi Anonymous
If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!
