Measure Counting Number of Completed Items

[littlemojopuppy]

Hi!  I'd like to ask for some help with something I've been trying to figure out since Friday.

I have a table of Enrollments...think of it as people who have signed up to take a program consisting of several Tasks.  I need two measures:

1. Calculate the Number of Enrollments where people have quit the Enrollment with having completed at least one Task
2. Calculate the Number of Enrollments where people have quit the Enrollment with having completed no Tasks

I'm going to focus on just one measure because the difference between the two would effectively be (Completed Tasks = 0) and (Completed Tasks > 0).

This is SQL that produces exactly what I need to produce in DAX:

-- Abandoned Enrollments With Completed Tasks
WITH		AbandonedEnrollments AS
			(SELECT		ProgramEnrollments.Id
			FROM		ProgramEnrollments
			WHERE		(ProgramEnrollments.Complete = 1) AND
						(ProgramEnrollments.CompletionCode = 2)),
			CompletedTaskCounts AS
			(SELECT		AbandonedEnrollments.Id,
						COUNT(DISTINCT ProgramEnrollmentTasks.Id) AS CompletedTasks
			FROM		AbandonedEnrollments
						INNER JOIN ProgramEnrollmentTasks ON
							(AbandonedEnrollments.Id = ProgramEnrollmentTasks.ProgramEnrollmentId)
			WHERE		(ProgramEnrollmentTasks.Completed = 1)
			GROUP BY	AbandonedEnrollments.Id),
			MergeData AS
			(SELECT		AbandonedEnrollments.Id,
						ISNULL(CompletedTaskCounts.CompletedTasks, 0) AS CompletedTasks
			FROM		AbandonedEnrollments
						LEFT OUTER JOIN CompletedTaskCounts ON
							(AbandonedEnrollments.Id = CompletedTaskCounts.Id))
SELECT		COUNT(DISTINCT MergeData.Id) AS AbandonedWithCompletedTasks
FROM		MergeData
WHERE		(MergeData.CompletedTasks > 0);

I am able to produce exactly the intended result as follows (and will provide code for each bullet momentarily):

• Create a calculated column in the ProgramEnrollments table calculating the number of completed tasks for each enrollment
• Create a measure for Abandoned Enrollments
• Calculate a second measure for Abandoned Enrollments With Completed Tasks that calculates Abandoned Enrollments with the filter of calculated column > 0
  
  

Here's the code for the calculated column

CompletedTasks = 
    CALCULATE(
        DISTINCTCOUNT(ProgramEnrollmentTasks[Id]),
        FILTER(
            RELATEDTABLE(ProgramEnrollmentTasks),
            ProgramEnrollmentTasks[Completed] = TRUE()
        )
    ) + 0

Here's the measure for Abandoned Enrollments

Abandoned Enrollments = 
    CALCULATE(
        [Enrollment Count],
        ProgramEnrollments[Complete] = TRUE(),
        ProgramEnrollments[CompletionCode] = 2,
        USERELATIONSHIP('Calendar'[Date], ProgramEnrollments[CompleteDate])
    )

Here's the measure for Abandoned Enrollments With Completed Tasks

Abandoned Enrollments With Completed Tasks = 
    CALCULATE(
        [Abandoned Enrollments],
        ISBLANK(ProgramEnrollments[CompletedTasks]) = FALSE()
    )

What I need is a measure that can reproduce the results of the SQL without use of a calculated column.  Where I keep getting stuck is trying to tie the three together and I have tried dozens of different things.  Instead of having the line

ISBLANK(ProgramEnrollments[CompletedTasks]) = FALSE() in the third measure - expressed in terms of SQL - I need it to query the ProgramEnrollmentTasks and obtain the count of Tasks where completed is 1 (true) for each individual ProgramEnrollment AND THEN filter those records included in calculating the Abandoned Enrollments measure.

I've been going around in circles on this for five days...I would certainly appreciate any help anyone could provide!  🙂

[Greg_Deckler]

So going to very much need sample data to test this out with. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[littlemojopuppy]

Hi @Greg_Deckler.  Sorry for the delayed response.

I can't share data because of HIPAA.  I like my job and would like to go back on Monday and my wife has a rule about not visiting me in prison...she's kind of a buzzkill.  But the SQL posted earlier contains all the logic necessary along with table and field names.

The solution I keep coming back to is along the lines of this.  You can substitute FILTER or CALCULATETABLE with the same outcome: "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

    CALCULATE(
        [Abandoned Enrollments],
        COUNTROWS(
            FILTER(
                RELATEDTABLE(ProgramEnrollmentTasks),
                ProgramEnrollmentTasks[Completed] = TRUE()
            )
        ) > 0
    )

Logically this makes all the sense in the world.  Except the problem is that in a filter expression for a CALCULATE statement, the left hand side of that expression must reference a column name, which this clearly is not.

The solution is how to I perform what would essentially be an SQL subquery within the context of a CALCULATE statement.  Someone points me towards that, I'm pretty sure I can figure the rest out.

[nmck86]

Hello! Can you share a sample without it being your real proprietary HIPPA data? Sample data can look and feel like your issue without it being a specific client or health/employment specific data that is proprietary. Usually takes a bit of time I know but still definitely easier for folks who can help out!

[littlemojopuppy]

I've removed everything except GUIDs, dates and times except for those things relevant.  There are two columns with headings in orange...those are calculated columns used to support the existing solution.

[amitchandak]

@littlemojopuppy ,

In case it is still not resolved. 

You can have these new columns and can drive your logic based on those

//new columns in ProgramEnrollments
 
 AbandonedEnrollments cnt = count(filter(AbandonedEnrollments,AbandonedEnrollments[Id] =ProgramEnrollments[ID]),AbandonedEnrollments[Id])
 completed Task cnt = count(filter(ProgramEnrollmentTasks,ProgramEnrollmentTasks[ProgramEnrollmentsId] =ProgramEnrollments[ID]
								&& ProgramEnrollmentTasks[Completed]=1 ),ProgramEnrollmentTasks[Id])
 total Task cnt = count(filter(ProgramEnrollmentTasks,ProgramEnrollmentTasks[ProgramEnrollmentsId] =ProgramEnrollments[ID]),ProgramEnrollmentTasks[Id])

[littlemojopuppy]

@amitchandak  your suggested columns invented tables that don't exist in the data model.  And the goal is to not use calculated columns for this, not create more.

[littlemojopuppy · Accepted solution]

I had an epiphany!  This works...

Enrollments With Completed Tasks = 
    CALCULATE(
        [Enrollment Count],
        SUMMARIZECOLUMNS(
            ProgramEnrollments[Id],
            "TaskCount", 
            COUNTX(
                FILTER(
                    ProgramEnrollmentTasks,
                    ProgramEnrollmentTasks[Completed] = TRUE()
                ),
                ProgramEnrollmentTasks[Id]
            )
        )
    )

In the screen snip the measure ending with "Redux" is the calculated column independent solution and the other one is based on calculated columns.  For all four statuses, they produce exactly the same value!  Woohoo!!!

[amitchandak]

Abandoned Enrollments With Completed Tasks = 
    CALCULATE(
        [Abandoned Enrollments],
        not(ISBLANK(ProgramEnrollments[CompletedTasks]))
    )

You have to use not

[littlemojopuppy]

Hi and thank you but what I'm aiming for is a solution that does not involve use of a calculated column.