Conditional count

[qwertzuiop]

Hello dear Power-BI-Community

Following problem to solve:

Let's assuem this table:

Person	Check
A	1
A	0
A	0
B	0
B	0
C	1
C	1
D	0
D	0
D	0

People must carry out checks.
Person A, for example, has to carry out the check 3x (1x done so far).
Person B has to carry out the check 2x (0x done so far)

And so on...

The aim is to use a measure to calculate how many people have not yet carried out a check.

Solution to this would be 2 (Person B & D)

Thank you very much for your contribution.

Cheers

qwertzuiop

[rsbin · Accepted solution]

@qwertzuiop ,

As @Syk , suggested, this is easiest done in two steps.  I would do it this way.

Create a new Calculated Column:

TotalChecks = CALCULATE( SUM(Checks[Check] ),
                     ALLEXCEPT( Checks, Checks[Person] ))

PersonCheckTotalChecks

A	1	1
A	0	1
A	0	1
B	0	0
B	0	0
C	1	2
C	1	2
D	0	0
D	0	0
D	0	0

Then create a new Measure:

0_Checks = CALCULATE( DISTINCTCOUNT( Checks[Person] ),
                     FILTER( Checks, Checks[TotalChecks] = 0 ))

Trust this works for you.

Regards,

[qwertzuiop]

Hello @Syk 

First of all - thank you very much for your support.
Unfortunately it is not yet solved.

With your measure, I count the distinct sum of people who have at least 1x 0.

In the table above it would be the person A,B,D based on your measure.
Person A should not to be counted because it has already carried out a control.

Goal is: Count the number of people who have not yet carried out any check.

Any ideas?

[Syk]

You can create a measure to sum your checks and then another to check for sum < 1. (You could also skip the first measure if your column is already summed)

It would be something like...

people_not_checked = CALCULATE(DISTINCTCOUNT('Table'[Person]),'Table'[Check]<1)