Forum Discussion

legendsfan's avatar
legendsfan
Frequent Visitor
9 years ago
Solved

Distinct Count if item Is This

I have a data set with Owner, Collaborator Type, and Folder.   Owners can own multiple folders and each folder has a Collaborator Type of external or managed.  I'd like to be able to get a distinct count of the number of owners 3 different ways.

  1. Owners who only have Managed folders
  2. Owners who only have External folders
  3. Owners who have both types

 

Right now I can sum the Distinct value of Owner+Managed or Owner+External but no idea how to count the sum of Owners that ONLY do one type or the other.  Any help is greatly appreciated.

Need Help

6 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 years ago

    Hi legendsfan

     

    So you want a distinct count of Owners that have exactly one Collaborator Type and that Collaborator Type = "Managed" (and the same for "External")?

     

    My first thought would be to do something like this:

    DistinctCount of Owners with Only Managed = 
CALCULATE (
    DISTINCTCOUNT ( Data[Owner] ),
    FILTER (
        VALUES ( Data[Owner] ),
        AND (
            CALCULATE ( HASONEVALUE ( Data[Collaborator Type] ) ),
            FIRSTNONBLANK ( Data[Collaborator Type], 0 ) = "Managed"
        )
    )
)

    or equivalently

    DistinctCount of Owners with Only Managed = 
COUNTROWS (
    FILTER (
        VALUES ( Data[Owner] ),
        AND (
            CALCULATE ( HASONEVALUE ( Data[Collaborator Type] ) ),
            FIRSTNONBLANK ( Data[Collaborator Type], 0 ) = "Managed"
        )
    )
)

    Let me know if that is of any use.

     

    Owen :)

    • legendsfan's avatar
      legendsfan
      Frequent Visitor
      9 years ago

      I don't believe this code is quite right based on the sample table I did a test on.  In this case I'd expect the number to be 2 since only Daisy and Edward have folders that are exclusively Managed.  Bob has Managed folders but he also has an External folder so he should not be counted.  Thoughts?