Forum Discussion
Solved
Help with calculated column or measure
I have a dataset...... simplified here:
| Case Number | Assistance | |||
| 1 | 1/1/2023 | Rent | ||
| 1 | 1/1/2023 | Util | ||
| 2 | 1/1/2023 | Rent | ||
| 2 | 1/1/2023 | Rent | ||
| 3 | 1/1/2023 | Util | ||
| 3 | 1/1/2023 | Util |
What I am trying to do is to calculate the number of labor hours per case in a report.
If same case number has Rent and Util assistance, the hours for the case would be 6
if same case number has Rent and Rent, the hours for the case would be 7
if same case number has Util and Util, the hours for the case would be 5
I'm having difficulty determining how to do the calculation here and whether or not this should be in a measure, a calculated table, or in a calculated column. Any assistance is greatly appreciated. My report desire is to sum the hours spent on a case for each assistance type.
jwessel Maybe:
Measure = VAR __Case = MAX('Table'[Case Number]) VAR __Table = SUMMARIZE('Table',[Assistance]) VAR __Count = COUNTROWS(__Table) VAR __Result = SWITCH(TRUE(), __Count = 2, 6, MAXX(__Table, [Assistance]) = "Rent", 7, 5 ) RETURN __Result
4 Replies
Apologies, column 2 is the date the case is created.
jwessel Maybe:
Measure = VAR __Case = MAX('Table'[Case Number]) VAR __Table = SUMMARIZE('Table',[Assistance]) VAR __Count = COUNTROWS(__Table) VAR __Result = SWITCH(TRUE(), __Count = 2, 6, MAXX(__Table, [Assistance]) = "Rent", 7, 5 ) RETURN __Result
