topic Re: GROUPBY Returning an Error, Please Help. in DAX Commands and Tips

GROUPBY Returning an Error, Please Help.

jaime_blackwell — Wed, 20 May 2020 01:33:03 GMT

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 receiving an error indicating that I've given the function 2 columns. I believe I have only given one column.

Can you please assist?

My code:

=GROUPBY(Table4
, Table4[Date]
,"TotalTimeForDay"
, SUMX( CURRENTGROUP(), Table4[Decimal Time])
)

My error:

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

Thank you!

Re: GROUPBY Returning an Error, Please Help.

Anonymous — Wed, 20 May 2020 01:41:11 GMT

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

Best

Re: GROUPBY Returning an Error, Please Help.

jaime_blackwell — Wed, 20 May 2020 01:50:41 GMT

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.

Re: GROUPBY Returning an Error, Please Help.

Anonymous — Wed, 20 May 2020 02:08:12 GMT

It's not a measure. It's a calculated table.

Best
D

Re: GROUPBY Returning an Error, Please Help.

jaime_blackwell — Wed, 20 May 2020 02:19:33 GMT

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!

Re: GROUPBY Returning an Error, Please Help.

Anonymous — Wed, 20 May 2020 02:34:29 GMT

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

Re: GROUPBY Returning an Error, Please Help.

jaime_blackwell — Wed, 20 May 2020 03:42:12 GMT

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.

Re: GROUPBY Returning an Error, Please Help.

Anonymous — Wed, 20 May 2020 09:30:29 GMT

[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

Re: GROUPBY Returning an Error, Please Help.

jaime_blackwell — Thu, 21 May 2020 05:25:33 GMT

Thank you so much D!