Count Distinct Rows where Distinct Rows have at least X occurences

[ZacWatkins]

Hello!

I have what is most likely a measure request for sorting some data out of my general tables and into a visualization.

Right now I have a table with Employees and SubmissionIDs, plus a bunch of other columns that don't matter right now. I need to create a distinct count of employees who have at least 3 submissions. Since filters will occassionally change what employees are available, and I'll need to use this count in a few other calculations, I believe it needs to be a measure.

So Data Structure effectively looks like:

Dan	1536
Dan	1231
Dan	5541
Dan	1233
Harold	1239
Jen	1238
Jen	1237
Jen	1236
Harold	1288
Betty	1773
Dan	1654

The resulting measure would say '2' (Dan and Jen, the other users haven't had enough submissions). DISTINCTCOUNT gets me how many unique users there are, which is great, but what I essentially need is a condition or filter to remove users that haven't made enough submissions.

I'm still pretty new to using measures with powerBI, so any help would be most appreciated!

[YukiK · Accepted solution]

This is one way to do this:

Distinct Cnt of Employees Where Submission gt 2 =
VAR __SubmissionsPerEmployee =
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[employeeName]
),
"@CntOfSubmissions", CALCULATE( COUNTROWS( 'Table' ) )
)

VAR __Result = CALCULATE( DISTINCTCOUNT( 'Table'[employeeName] ), FILTER( __SubmissionsPerEmployee, [@CntOfSubmissions] > 2 ) )
RETURN
__Result

[ZacWatkins]

This one worked a treat! Thank you so much!

[YukiK]

Glad to be some help! Please give it a thums up too!

[smpa01]

@ZacWatkins  you can use a measure like this

Measure =
CALCULATE (
    DISTINCTCOUNT ( tbl[Emloyee] ),
    CALCULATETABLE (
        tbl,
        FILTER (
            tbl,
            CALCULATE ( COUNT ( tbl[Submission] ), ALLEXCEPT ( tbl, tbl[Emloyee] ) ) >= 3
        )
    )
)

[ZacWatkins]

This one worked with my little test data sheet, but when I took it to the actual table it returned some odd numbers, I'll keep poking around and reply here if I figure out what went wrong. Thank you for your response!