cancel
Showing results for
Did you mean:

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.

Helper II

## 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.

1 ACCEPTED SOLUTION
Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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 !!

Helper II

thanks Greg, I will give that a try.

Helper II

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors