Forum Discussion

ZacWatkins's avatar
ZacWatkins
New Member
4 years ago
Solved

Count Distinct Rows where Distinct Rows have at least X occurences

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 ...
Need Help
  • YukiK's avatar
    YukiK
    4 years ago

    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

     

     

smpa01's avatar
smpa01
Community Champion
4 years ago

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's avatar
ZacWatkins
New Member
4 years ago

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!