Use SUM and COUNT in a measure

[Anonymous]

Hi,

I am trying to count people who have not submitted more than 10 Sales Reports.

I have a column called No_Reports = 1 as Not submitted and 0 as submitted

I was trying to use a DAX as below =

= CALCULATE(COUNTDistinct(Person[PersonID]), FILTER(Person[No_Report = 1))

How do I then look for 10 reports? Please suggest.

[Anonymous]

@Anonymous 

Try using an IF()

10Reports = CALCULATE(COUNTDistinct(Person[PersonID]), FILTER(Person[No_Report = 1))) < 10, "YAY", NAY")

Not sure what COUNTDistinct is.

Thanks!
A

[Anonymous]

COUNT distinct is used to count distinct people who are missing more than 10 reports.

Above DAX doesn't seem to work with IF and COUNT in calculate

[Anonymous]

@Anonymous 

I suggest you will give some sample data.

Also, I couldn't find any function named countdistinct. There is, however, a DISTINCTCOUNT() function.

There is no reason for the formula not to work.

Thanks!
A

[Anonymous]

Yes its distinctCount.

Here is the Sample for which I am struggling with DAX.

To make it simple lets change it to 2 Reports not submitted.

Nos of Employees who have not submitted more than 2 reports

Table Name = Person

Expected Result = 2  (That will be for Emp 1000,3000)

Report Not Submitted = 1  ie "Not Submitted"

Person_EmpID	WK SttratDate	Report Not Submitted
1000	7/7/2019	1
1000	7/14/2019	0
1000	7/21/2019	1
1000	7/28/2019	1
2000	7/7/2019	1
2000	7/14/2019	0
2000	7/21/2019	0
2000	7/28/2019	0
3000	7/7/2019	1
3000	7/14/2019	0
3000	7/21/2019	1
3000	7/28/2019	1

[Anonymous · Accepted solution]

Hey @Anonymous 

Here is the calculated collumn:

SUBMITTED? =
VAR NUM_OF_SUB =
    CALCULATE (
        COUNTROWS ( T23 ),
        FILTER (
            T23,
            T23[Person_EmpID]
                = EARLIER ( T23[Person_EmpID] )
        ),
        FILTER (
            T23,
            T23[Report Not Submitted] = 1
        )
    )
RETURN
    IF (
        NUM_OF_SUB >= 2,
        "NOT Submitted",
        "Submitted"
    )

Cheers!
A