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
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]))Thanks, it works, I just have one question, going back to the old measure:
Count of Entry Date for Task = CALCULATE(DISTINCTCOUNT(GetPlannedHoursForPeriodOfDaysFunc[DateEntry]),all(GetPlannedHoursForPeriodOfDaysFunc),VALUES(GetPlannedHoursForPeriodOfDaysFunc[TaskID]), VALUES (GetPlannedHoursForPeriodOfDaysFunc[UserID]))
What is the values function doing within the calculate, I can't seem to wrap my brain around it
I was going to answer that, but I was on my ipad and typing is a bit slow :-).
In short, ALL(table) removes all filters from the current filter context. ie, any filter that is coming from the visualisation is removed from the table specified. The VALUES(table[column]) function inspects the visible values in the current filter context (before being modified by ALL). If you were to "peek" to see what VALUES returns, it would return a single column table containing all the rows in the current filter context. Importantly this new virtual VALUES table also has a link to the source table, so in effect it filters the original table the same way a lookup table does. So VALUES reapplies filters from the current filter context after everything was removed using ALL.
ALLEXCEPT as its name suggests doesn't remove filters from the entire table, but instead leaves filters on the columns specified.
I tend to use ALL(), VALUES() if there is a single column I want to retain. I tend to use ALLEXCEPT(table, table[exceptions]) if there are more columns to keep and only a few to remove.
I hope that makes sense.