Forum Discussion

jaime_blackwell's avatar
jaime_blackwell
Icon for Helper I rankHelper I
6 years ago
Solved

GROUPBY Returning an Error, Please Help.

Hi Community,   I'm having difficulty creating a measure using DAX. I want to group records by DATE and get TOTAL DECIMAL TIME of each group.   So far I'm using the GROUPBY function, but I'm rece...
  • Anonymous's avatar
    Anonymous
    6 years ago
    [Total Time] = SUM( T[Decimal Time] )

[Billable Time] =
var __breakTime = 1 * time(0, 30, 0)
var __result =
	SUMX(
		SUMMARIZE(
			T,
			T[UserID],
			T[Date]
		),
		var __time = [Total Time]
		var __deduction = ( __time > 5 ) * __breakTime
		return
			__time - __deduction
	)
return
	__result
	
[Billable Time %] =
	DIVIDE(
		[Billable Time],
		[Total Time]
	)

     

    Best

    D

jaime_blackwell's avatar
jaime_blackwell
Icon for Helper I rankHelper I
6 years ago

Hi D,

 

Thanks so much for your response. I have keyed the below into my measure however I still receive the same error. Do you know why this is occuring?

 

Code:

=ADDCOLUMNS(VALUES(Table4[Date]), "TotalTimeForDay", CALCULATE(SUM(Table4[Decimal Time])))

 

Error:

Calculation error in measure 'Table4'[Test1]: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

Thank you.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago
It's not a measure. It's a calculated table.

Best
D
  • jaime_blackwell's avatar
    jaime_blackwell
    Icon for Helper I rankHelper I
    6 years ago

    Hi D,

    The reason why I'm looking to achieve this within a measure is because the desired output is a measure and not a calculated table. The end result in my case is Percentage billable per user per day. The questions above is only part of the calculation (eg. I also need to minus 30 minutes from each user's day if they worked more than 5 hours that day).

    Hope this makes sense. So with that said, do you know any way to achieve the desired result in the context of a measure rather than calculated table?

    Thanks again!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      I believe your requirements for a measure are not correct. You say:

      "I want to group records by DATE and get TOTAL DECIMAL TIME of each group."

      Grouping by date does not contribute anything if you want a measure. You can write:

      sum( T[Decimal Time] )

      and that's it. The rest is a matter of slicing and dicing.

      Best
      D
      • jaime_blackwell's avatar
        jaime_blackwell
        Icon for Helper I rankHelper I
        6 years ago

        Hi D,

         

        In that case, how would you suggest I tackle the below?

         

        I have some time record data which includes Date, User, Time(duration) and whether the line is billable time or not. The dataset has many different users and contains a whole week of data. I would like to calculate the percentage of billable time for each user. However, if the person worked more than 5 hours on a given date, the calculation needs to deduct a 30 minute lunch break from their 'non-billable' time. I have almost succeeded with a measure, but the date is not taken into account. So the time taken out to account for lunch breaks is innacurate. Currently, the calculation sees that User A has worked 38 hours total, so it minuses 30 minutes. I need it to check how long the person worked on every date, and minus 30 minutes for every time the person worked more than 5 hours.

         

        The reason why I want this to be a dax measure is because the calculation will also be used to populate a Pivot Table in Excel. 

        Do you have any suggestions?

         

        Thank you again.