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 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:

 

Dan1536
Dan1231
Dan5541
Dan1233
Harold1239
Jen1238
Jen1237
Jen1236
Harold1288
Betty1773
Dan1654

 

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!

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

     

     

5 Replies

  • 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!

  • YukiK's avatar
    YukiK
    Impactful Individual
    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

     

     

      • YukiK's avatar
        YukiK
        Impactful Individual
        4 years ago

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