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 All,
I am trying to generate a resource allocation map/detail for a given task based on the below sample details.
The transformation I need is as below:
As you can see I need to insert one row for the combination of ID, Taks name, Resource and allocation hours per each date that falls between start and end date inclusive.
Any help and/or suggestions on the above problem will be much appreciated.
Thanks.
Solved! Go to Solution.
Hi @anandav
This calculated table might get close :
Table = VAR myCalendar = ADDCOLUMNS( CALENDARAUTO() , "Day Type" , IF(WEEKDAY([Date])<6,"Weekday","Weekend") ) RETURN ADDCOLUMNS( FILTER( CROSSJOIN( myCalendar ,Table1) , 'Table1'[Start Date] <= [Date] && 'Table1'[End Date] >= [Date] ), "Allocated Hrs",IF([Day Type]="Weekend",0,8 * ('Table1'[Allocation %]/100)) )
Hi,
You may refer to my solution here - https://1drv.ms/u/s!AnsG4LrWCkhUn71E5iXvudjRF9MSKg
Hope this helps.
Please refer to my post here, which will guide you through step-by-step "expanding date ranges" so there's a row for each date.
Hi @anandav
This calculated table might get close :
Table = VAR myCalendar = ADDCOLUMNS( CALENDARAUTO() , "Day Type" , IF(WEEKDAY([Date])<6,"Weekday","Weekend") ) RETURN ADDCOLUMNS( FILTER( CROSSJOIN( myCalendar ,Table1) , 'Table1'[Start Date] <= [Date] && 'Table1'[End Date] >= [Date] ), "Allocated Hrs",IF([Day Type]="Weekend",0,8 * ('Table1'[Allocation %]/100)) )
Hi,
I am trying to solve some similar kind of problem so I am trying to understand your code.
Table1'[Start Date] <= [Date] && 'Table1'[End Date] >= [Date]
Can you explain which Date column are we refering here ?
TThan you in advance
Hi,
It is probably the Date field from the Calendar Table.
Thank you for the prompt reply. It works fine.
You are truly a "super contributor" taking time to help with such detail code.
I tried the DAX code but I get an error "CalendarAuto function can not find a base column type of DateTime type in the model" and then figured out I haven't set the data type to Date in my original table - from one of your another post.
Thanks a lot!
Hi @Phil_Seamark,
I have a matrix which displays the resource allocations from the table created from the code you gave.
Since matrix does not allow conditional formatting on row subtotals, I was trying to add a measure to calculate the daily total allocated hours for a given resource for a given day. But I could not get the measure work correct.
Data Model:
What I need (as inidicated in red):
Then I can disable row subtotals and do conditional formatting on the Total Hours column.
I cannot figure out how to write the measure with the right filters using CALCULATE.
Any help will be greatly appreciated.
Hi @anandav
Share your file download link here.
Hi @Ashish_Mathur,
The source file is simple as described in the post.
ID | Task Name | Resource | Allocation % | Start Date | End Date |
1 | Task 1 | Res1 | 40 | 01/09/2017 | 20/09/2017 |
2 | Task 2 | Res2 | 40 | 01/09/2017 | 20/09/2017 |
3 | Task 3 | Res1 | 40 | 01/10/2017 | 20/10/2017 |
4 | Task 4 | Res3 | 40 | 01/10/2017 | 20/10/2017 |
Based on Phil's DAX code this source is tranformed in Resource Allocation table (see above post by Phil).
I am generating the matirx based on this dynamically created table.
Not sure I have addressed what you needed but hope it is clear.
Hi,
You may refer to my solution here - https://1drv.ms/u/s!AnsG4LrWCkhUn71E5iXvudjRF9MSKg
Hope this helps.
Hi @Ashish_Mathur,
Hours Worked 1 is what I wanted. Thanks for the quick solution and taking time to put out a solution file.
I was strugling on the filter part of the CALCULATE function and ALL(taskName) removes the filter on Task Name but keeps the Date filter. Excatly what I needed.
Thanks a lot!
You are welcome.
Hi @Ashish_Mathur,
One more help.
How can I achieve the same (total hours per resource per day) in a column?
The measure works well for Matirx. But I needed to create a new Column which does the same thing as Hours Worked 1 measure.
The requirement is so that the reosurce manager can filter by resource availability to see which dates are a given resource is available.
Hope you can help.
Hi,
i am not sure of what you want but just swith your visual from a matrix to a table and the hours worked1 will become a column which can now be filtered.
Hi @Ashish_Mathur,
What I wanted to do is to create a column instead of measure.
So instead of Hours Worked 1 as a measure, I would like to create a new colum.
Hi @anandav,
What purpose will that serve that the Table visual wouldn't? Wouldn't your requirement be met by the Table visual which i suggested above?
Hi @Ashish_Mathur,
Yes, the measure serves the purpose.
I am keen to learn the DAX code for how this could be done using a column.
Hi @anandav,
This should be the calculated column formula in the Data Model i.e. PowerPivot
=CALCULATE(SUM(Data[Allocated hours]),FILTER(Data,Data[Resource]=EARLIER(Data[Resource])&&Data[Date]=EARLIER(Data[Date])))
Thereafter, write this simple measure
=SUM(Data[Column])
Hope this helps.
You are welcome.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |