Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ukeasyproj
Helper II
Helper II

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

 

 

 

 

 

 

 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

8 REPLIES 8

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

 

 

avg.png

 

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.

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@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:

 

test3.PNG

 

 

 

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)



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@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

Try this

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@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

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.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.