Forum Discussion
looking for a workaround for using countrows in a calculated column in DQ mode
Some background info:
What I am trying to do is, for a given taskid+userid, I am trying to calculate the [# of days]
[# of days] will then be used to calculate [estimated hours per day] which is basically just [estimated hours]/[# of days]
My issue is that I have the following dax formula, but it does not seem to be allowed in a calculated column in DQ mode (tested and works in import mode, but unfortunately my organization only allows DQ mode for now):
# of days = COUNTROWS(FILTER(GetPlannedHoursForPeriodOfDaysFunc, EARLIER(GetPlannedHoursForPeriodOfDaysFunc[UserID]) = GetPlannedHoursForPeriodOfDaysFunc[UserID] && EARLIER(GetPlannedHoursForPeriodOfDaysFunc[TaskID]) = GetPlannedHoursForPeriodOfDaysFunc[TaskID] ))
The table im dealing with as follows:
user id| task id| entry date| estimated hours |
------------------------------
1 | 1 | Jan 1st | 60
1 | 1 | Jan 2nd | 60
1 | 1 | Jan 3rd | 60
1 | 1 | Jan 4th | 60
1 | 2 | Jan 1st | 20
1 | 2 | Jan 2nd | 20
For a given user ID and task ID, I want to calculate the number of occurences of the entry date in the table,
in the above scenario, it should be 4 and 2 but how I want it stored is as follows (I want it stored at the row level, so I think I can only use a calculated column)
user id| task id| entry date| estimated hours | # of days | hours per day
--------------------------------------------------------------------------------
1 | 1 | Jan 1st | 60 | 4 | 15
1 | 1 | Jan 2nd | 60 | 4 | 15
1 | 1 | Jan 3rd | 60 | 4 | 15
1 | 1 | Jan 4th | 60 | 4 | 15
1 | 2 | Jan 1st | 20 | 2 | 10
1 | 2 | Jan 2nd | 20 | 2 | 10
The reason I need to stored at the row level like this is because later I want to aggregate the following way:
user id| entry date| hours per day
--------------------------------------------------------------------------------
1 | Jan 1st | 25
1 | Jan 2nd | 25
1 | Jan 3rd | 15
1 | Jan 4th | 15
My current solution only works in import mode, but I am wondering if anyone out there knows of a way to get this working in DQ mode
appreciate the help
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])
8 Replies
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)
