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

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Table = 
ADDCOLUMNS( 
	VALUES( T[Date] ),
	"TotalTimeForDay",
		CALCULATE(
			SUM( T[Decimal 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!