cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Patryk Frequent Visitor
Frequent Visitor

Calculate Distinct Count of Filtered Values if bigger than other value

Hi All,

 

Fairly new to PBI so hopefuly it will be easy to someone with more experience.

 

I have following data:

 

AreaSite Collection URLUser EmailUser RoleAD User Enabled
Area 1 URL1mail3@gmail.comFSOYes
Area 1 URL1mail1@mail.comSiteAdminsYes
Area 1 URL1mail2@mail.comSiteAdminsYes
Area 1 URL1mail3@gmail.comPrimary Site OwnerNo
Area 1 URL2mail1@mail.comTechnical Site OwnersYes
Area 1 URL2mail1@mail.comFSOYes
Area 1 URL2mail4@mail.comTechnical Site OwnersYes
Area 1 URL3mail5@mail.comFSOYes
Area 1 URL4mail5@mail.comSiteAdminsYes
Area 1 URL4mail5@mail.comSiteAdminsYes
Area 1 URL4mail5@mail.comTechnical Site OwnersYes
Area 1 URL4mail6@mail.comTechnical Site OwnersYes
Area 1 URL4mail7@mail.comTechnical Site OwnersYes
Area 1 URL4mail8@mail.comFSOYes
Area 1 URL4mai9@mail.comFSOYes
Area 1 URL5mail9@mail.comTechnical Site OwnersYes
Area 1 URL6mail1@mail.comSiteAdminsNo
Area 1 URL6mail2@mail.comTechnical Site OwnersYes
Area 2URL7mail3@gmail.comPrimary Site OwnerYes
Area 2URL7mail2@mail.comTechnical Site OwnersYes
Area 2URL7mail9@mail.comSiteAdminsYes

 

What I need to report is all sites (Site URL) that have more than 4 owners in specified areas.

 

However, as owner I only treat the User roles of "FSO" or "Tehcnical Site Owners". None of the other values should be counted. Moreover if the same person (User Email) has both FSO and Technical Site Owner roles on the same site url they should only be counted once. On top of that, the Owner needs to have active account (AD User Enabled = "yes")

 

I tried experimenting with distinctcount and Filter functions but only got so far as to calculate all unique owners (FSO or Technical Site Owners) per Area, not joined by Site URL.

 
Correct end values for the above example would be:
 
Site Collection URLNumber of Owners
URL11
URL22
URL31
URL45
URL51
URL61
URL71

 

That means that in the end I only will be displaying URL4 as its the only one meeting all criteria (more than 4 owners).

 

I imagine this will be multi-step calculation and might need adding measured columns but I am just not sure where to start.

 

Any help appreciated,

Patryk

3 REPLIES 3
tjlundquist1 Frequent Visitor
Frequent Visitor

Re: Calculate Distinct Count of Filtered Values if bigger than other value

Try this :

 

Column = CALCULATE(DISTINCTCOUNT(Table1[(User Role]), ALLEXCEPT(Table1,Table1[(Site Collection URL]) )
 
Your tables may be labeled different, but hopfully this makes sense.
 
 
 
Patryk Frequent Visitor
Frequent Visitor

Re: Calculate Distinct Count of Filtered Values if bigger than other value

Sorry, only now was able to test it.


Sadly what the proposed calculation only counts the available roles (which there are 5 of) therefore the result is 5 for all areas.

 

What I came up to is to count unique owners with the roles I want to count:

 

Sites with 4+ Owners WIP = CALCULATE (DISTINCTCOUNT('SharePoint 4+ Owners'[User Email]),
Filter('SharePoint 4+ Owners',
    ('SharePoint 4+ Owners'[AD User Enabled] = "Yes" &&
    'SharePoint 4+ Owners'[User Role] = "FSO")||
    ('SharePoint 4+ Owners'[AD User Enabled] = "Yes" &&
    'SharePoint 4+ Owners'[User Role] = "Technical Site Owners")
    ))

Now I need to somehow incorporate the grouping by Site Collection URL and only counting these that have 4+ unique owners.

tjlundquist1 Frequent Visitor
Frequent Visitor

Re: Calculate Distinct Count of Filtered Values if bigger than other value

@Patryk - See below. I tested the formula, works as you requested.. 

 

# of Owners = CALCULATE(DISTINCTCOUNT(Table1[User Role]), ALLEXCEPT(Table1,Table1[Site Collection URL]))
 

Please tag as a solution if this works for you. Thanks

Capture.JPG

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 166 members 1,828 guests
Please welcome our newest community members: