Measure Counting Number of Records in Related Table

[littlemojopuppy]

Hi!  I'd like to ask for some help with something.  I need to create a measure that calculates a a value based on the number of records in a related table.

I could cheat and create a calculated column like this and it works exactly as intended:

Started but Abandoned Enrollments = 
    CALCULATE(
        [Abandoned Enrollments],
        ProgramEnrollments[CompletedTasks] = 0
    )

 If I absolutely have to, I'll do it this way.  But I don't believe that there is no way to solve this without the calculated column.

Here's the code of what I've been trying...

Started but Abandoned Enrollments = 
    CALCULATE(
        [Abandoned Enrollments],
        COUNTROWS(
            CALCULATETABLE(
                RELATEDTABLE(ProgramEnrollmentTasks),
                ProgramEnrollmentTasks[Completed] = TRUE()
            )
        ) = 0
    )

This produces the error message "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."  I've looked on this forum and Google in general and have found suggestions similar to this that have suggested using the FILTER function.  Tried that have gotten error messages that "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." and I've also tried a nested CALCULATE statement that resulted in a similar error message.  I've also tried creating a variable for the CALCULATETABLE/FILTER/nested CALCULATE statements and that didn't work either, with the same error messages.

I know that the CALCULATETABLE expression is valid - I created a table using that DAX just to make sure it was a valid statement.  The error comes when I try to COUNTROWS of the table produced in the code.  I should add...I've also tried DISTINCTCOUNT and COUNTX to try to count the related records with the exact same result.

Could anyone please offer some suggestions on how to correct this?  I'm going crazy over this!  😣  Thank you!

[Ashish_Mathur]

Hi,

Share your sample data and show the expected result.

[littlemojopuppy]

Hello!  Thank you for replying!

I can't share sample data because that would violate just about every HIPAA rule there is and I don't want to be fined or go to jail.  I can share a screen snip of the data model, and with the code I have and will provide along with a screen shot of the data model...hopefully that will be enough?

Code for the calculated column if I took the short cut...

CompletedTasks = 
    COUNTX(
        RELATEDTABLE(ProgramEnrollmentTasks),
        ProgramEnrollmentTasks[Id]
    )

I've already shared code for the calculated column solution and for what I'm trying for with a measure.  There are two measures...one measure would be CompletedTasks = 0 and the other would be CompletedTasks > 0.

This is the data model...

All the tables off to the left are irrelevant...they're just to keep measures organized.

Expected result...the fields labeled "dummy" are based on the calculated column solution.  They add up to the measure "Abandoned Enrollments"

[littlemojopuppy]

So I'm spending my Saturday reviewing courses on DAX because I am rusty as Hell.  And low and behold...here is my problem!

The CALCULATE function requires a column name on the left hand side of a filtering statement.  And I have the function COUNTROWS working on a CALCULATE, FILTER or CALCULATETABLE statement, depending of which iteration of banging my head against a wall you're looking at.  My logic is completely correct...but it doesn't work with DAX.

I'm happy to send a six pack of Laguinitas to whomever can help me figure out how to solve this!  😄

[amitchandak]

Try in the filter clause

Started but Abandoned Enrollments = 
    CALCULATE(
        [Abandoned Enrollments],
        filter(Table,COUNTROWS(
            CALCULATETABLE(
                RELATEDTABLE(ProgramEnrollmentTasks),
                ProgramEnrollmentTasks[Completed] = TRUE()
            )
        ) = 0)
    )

Not sure of table name so given table

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[littlemojopuppy]

Thank you!  😊  This is at least syntactically correct...no errors were thrown.  But the counts are wrong.  There is a second measure for never started where the count = 0.  And (greater than zero) + (equal to zero) should equal the measure [Abandoned Enrollments]

I can try working with this tomorrow and we'll see what I can come up with...