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])
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
Try this
Count of Entry Date for Task = CALCULATE(DISTINCTCOUNT(Table[entry date]),allexcept(Table,table[entry date]))