Forum Discussion
looking for a workaround for using countrows in a calculated column in DQ mode
Hi
I have been thinking - and I wanted to give you a different solution in case you prefer it. This solution assumes 1 flat table exactly as you explained in the OP. If you write these measures I think it will do what you want.
Total Task Hours = max(Table[estimated hours])
Count of Entry Date for Task = CALCULATE(DISTINCTCOUNT(Table[entry date]),all(Table),VALUES(Table[task id]))
Daily Hours for Task = sumx(SUMMARIZE(Table,Table[entry date],Table[task id]),[Total Task Hours]/[Count of Entry Date for Task])
There is another way. The reason you are having difficulty with the formulas is because your source data is not in the best "shape" for the purpose. The estimated hours seems to be at the user/task level, yet the data is repeated down the column. I would change the data structure on load.
1. Load a table containing the user, task, estimates - 1 row per user/task combination
2. Load a table of user/task/dates with no estimates - 1 row per date/user/task
3. Load a user table
4 load a task table
Join them all togther and write the following formulas
Count of Days = DISTINCTCOUNT(EntryData[entry date])
Estimate Hours = SUM(Estimates[estimated hours])
Avg Actual Hours Per Day = sumx(TaskID,DIVIDE([Estimate Hours],[Count of Days]))
Here is a sample workbook
https://www.dropbox.com/s/mgukvgs42mbp08t/avg%20days.pbix?dl=1
There are other approaches, but personally I believe in investing up front effort in getting the design right, then everything else is can be easier.
Hi matt, I took a look at your suggestions and I agree that my data model needs some work
One question though, Ideally this is how I would like to aggregate the data, but I am not sure how to do it using your data model:
Hi. Sorry, you were very clear what you wanted - sorry for not catching this.
If you write this measure and put it in a visual it should work.
Avg per Day = CALCULATE([Avg Actual Hours Per Day],EntryData)
Hi
I have been thinking - and I wanted to give you a different solution in case you prefer it. This solution assumes 1 flat table exactly as you explained in the OP. If you write these measures I think it will do what you want.
Total Task Hours = max(Table[estimated hours])
Count of Entry Date for Task = CALCULATE(DISTINCTCOUNT(Table[entry date]),all(Table),VALUES(Table[task id]))
Daily Hours for Task = sumx(SUMMARIZE(Table,Table[entry date],Table[task id]),[Total Task Hours]/[Count of Entry Date for Task])
Hey Matt, I forgot to mention that multiple users can be assigned to a task so what I did was I changed your measure to the following:
Count of Entry Date for Task = CALCULATE(DISTINCTCOUNT(GetPlannedHoursForPeriodOfDaysFunc[DateEntry]),all(GetPlannedHoursForPeriodOfDaysFunc),VALUES(GetPlannedHoursForPeriodOfDaysFunc[TaskID]), VALUES (GetPlannedHoursForPeriodOfDaysFunc[UserID]))
The solution seems to be working but I just want to confirm if this is the correct way to go about it