Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
[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
Table =
ADDCOLUMNS(
VALUES( T[Date] ),
"TotalTimeForDay",
CALCULATE(
SUM( T[Decimal Time] )
)
)
Best
D
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.
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!
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.
[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
Thank you so much D!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |