Using a measure to filter

[setis]

Dear experts, 

I need some help here. I hope that you can help me. 

I am trying to calculate the assessments where only 1 employee has been active. 

I have a list of assessments and a column with the name of the employee. 

To calculate the nr of employees per case, I'm using the measure: 

NrEmployees = DISTINCTCOUNT(Assessments[AssessmentBy])

Now I need a measure to show me the assessments where NrEmployees = 1

I'm using:

OneEmployee =
VAR nrEmp = DISTINCTCOUNT(Assessments[AssessmentBy])
VAR CountAssessments = COUNT(Assessments[AssessmentID])
return
CALCULATE([CountAssessments];FILTER(Assessments;NrEmp=1))

the result I'm obtaining is the full nr of assessments and not only those where the nr of employees =1.

What am I doing wrong?

[Greg_Deckler]

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[setis]

Dear @Greg_Deckler ,

Thanks a lot for reaching out. I've looked at your proposed method and I'll try to use some time to do it like that. Still I think that I probably didn't explain my problem good enough. 

All the data I'm working on is in one table. 

I created a file replicating my problem here in case you had the time to look at it. I would appreciate it 🙂

https://drive.google.com/file/d/1wHsv9sSIbiHBRMpG6ty5pqRRTItPooJs/view?usp=sharing

As you can see, the measure NrEmployees, is just a DISTINCTCOUNT of the name of the employee.

What I'm trying to do is just to create a measure counting when the above measure is 1 and another one when it is >0.

Thank you in advance!

[Anonymous · Accepted solution]

@setis -

First, semantics: In your example, "One Employee" is a Measure, while nrEmployees and nrCases are Variables.

Each of those variables is a assigned a scalar value. Therefore, the CALCULATE function has no effect.

Here is a possibility for the Several Employees Measure:

Several Employees =
VAR summed =
    SUMMARIZE (
        Assessments,
        Assessments[CaseID],
        "NumEmployees", DISTINCTCOUNT ( Assessments[Employee] )
    )
RETURN
    SUMX ( FILTER ( summed, [NumEmployees] > 1 ), [NumEmployees] )

Cheers!

Nathan

[setis]

Dear @Anonymous THANK YOU SO MUCH!

It wasn't as simple as I thought it would be.