Help with calculated column or measure

[jwessel]

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.

[Greg_Deckler · Accepted solution]

@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

[jwessel]

Hi Greg.  thanks again for that information.  While I didn't use it specifically due to some other factors, it definitely set me on the right path for a solution !!

[jwessel]

thanks Greg, I will give that a try.

[jwessel]

Apologies, column 2 is the date the case is created.