looking for a workaround for using countrows in a calculated column in DQ mode

[ukeasyproj]

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

[MattAllington]

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.

[ukeasyproj]

@MattAllington

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:

[MattAllington]

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)

[MattAllington · Accepted solution]

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])

[ukeasyproj]

@MattAllington

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

[MattAllington]

Try this

 
Count of Entry Date for Task = CALCULATE(DISTINCTCOUNT(Table[entry date]),allexcept(Table,table[entry date]))

[ukeasyproj]

@MattAllington

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

[MattAllington]

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.