Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
jwessel
Helper II
Helper II

Help with calculated column or measure

I  have a dataset...... simplified here:

Case Number Assistance  
11/1/2023Rent  
11/1/2023Util  
21/1/2023Rent  
21/1/2023Rent  
31/1/2023Util  
31/1/2023Util  

 

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
Greg_Deckler
Super User
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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

thanks Greg, I will give that a try.

jwessel
Helper II
Helper II

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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