DAX Count by person if a sum is greater than a set value per day

[jguercio]

Hi All,

New to DAX here and trying to figure my way through it, but I am stumped and on a deadline.

Business Problem: We serve clients and count the duration of minutes. Every service is documented in a contact note. If the sum of the duration of all notes written for a client is great than or equal to 15 minutes we can bill, but we can only generate one bill per day and we can only invoice once per month.

In short, I need a binary 1 or 0 per client who has reached their 15 minutes in a day and I need a monthly sum of how many days we can bill for.  If the client is served in a day program the duration threshold must be 60 minutes.

The measure below almost does it, except it sums all notes greater than 15 in a day. So a single day is showing a number greater than 1. Only the row subtotal also overstates.

Billable  = IF([Service Type]="Psych Rehab" && [Program]<>"BH - PRP Day Program" && SUMX('FACT-SD_Service Data',[Duration (Minutes)])>=15,1,IF([Service Type]="Psych Rehab" && [Program]="BH - PRP Day Program" && SUMX('FACT-SD_Service Data',[Duration (Minutes)])>=60,1,0))

Model Relationships

[kentyler · Accepted solution]

Maybe if you split up your processing it will be easier to see
VAR not_day_program = IF([Service Type]="Psych Rehab" && [Program]<>"BH - PRP Day Program" ,1,0)

VAR day_program = IF([Service Type]="Psych Rehab" && [Program]="BH - PRP Day Program")

VAR day_total_minutes = CALCUATE(SUMX('FACT-SD_Service Data',[Duration (Minutes)]), day_program)
VAR not_day_total_minutes =CAlCULATE(SUMX('FACT-SD_Service Data',[Duration (Minutes)]),not_day_program)
RETURN = if(day_total_minutes > 60 || not_day_total_minutes > 60,1,0)

[kentyler · Accepted solution]

Maybe if you split up your processing it will be easier to see
VAR not_day_program = IF([Service Type]="Psych Rehab" && [Program]<>"BH - PRP Day Program" ,1,0)

VAR day_program = IF([Service Type]="Psych Rehab" && [Program]="BH - PRP Day Program")

VAR day_total_minutes = CALCUATE(SUMX('FACT-SD_Service Data',[Duration (Minutes)]), day_program)
VAR not_day_total_minutes =CAlCULATE(SUMX('FACT-SD_Service Data',[Duration (Minutes)]),not_day_program)
RETURN = if(day_total_minutes > 60 || not_day_total_minutes > 60,1,0)

[jguercio · Accepted solution]

I reworked the data model and this code seems to work:

OnSite Billable Days = CALCULATE(

COUNTROWS(

CALCULATETABLE(

SUMMARIZE(

PsychRehab,

PsychRehab[Client ID],

PsychRehab[Contact Date],

"Threshold",

SUM(PsychRehab[Duration (Minutes)])),

PsychRehab[Program]="BH - PRP Day Program",

PsychRehab[Duration (Minutes)]>=60)))