Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Fairly new to PBI so hopefuly it will be easy to someone with more experience.
I have following data:
Area | Site Collection URL | User Email | User Role | AD User Enabled |
Area 1 | URL1 | mail3@gmail.com | FSO | Yes |
Area 1 | URL1 | mail1@mail.com | SiteAdmins | Yes |
Area 1 | URL1 | mail2@mail.com | SiteAdmins | Yes |
Area 1 | URL1 | mail3@gmail.com | Primary Site Owner | No |
Area 1 | URL2 | mail1@mail.com | Technical Site Owners | Yes |
Area 1 | URL2 | mail1@mail.com | FSO | Yes |
Area 1 | URL2 | mail4@mail.com | Technical Site Owners | Yes |
Area 1 | URL3 | mail5@mail.com | FSO | Yes |
Area 1 | URL4 | mail5@mail.com | SiteAdmins | Yes |
Area 1 | URL4 | mail5@mail.com | SiteAdmins | Yes |
Area 1 | URL4 | mail5@mail.com | Technical Site Owners | Yes |
Area 1 | URL4 | mail6@mail.com | Technical Site Owners | Yes |
Area 1 | URL4 | mail7@mail.com | Technical Site Owners | Yes |
Area 1 | URL4 | mail8@mail.com | FSO | Yes |
Area 1 | URL4 | mai9@mail.com | FSO | Yes |
Area 1 | URL5 | mail9@mail.com | Technical Site Owners | Yes |
Area 1 | URL6 | mail1@mail.com | SiteAdmins | No |
Area 1 | URL6 | mail2@mail.com | Technical Site Owners | Yes |
Area 2 | URL7 | mail3@gmail.com | Primary Site Owner | Yes |
Area 2 | URL7 | mail2@mail.com | Technical Site Owners | Yes |
Area 2 | URL7 | mail9@mail.com | SiteAdmins | Yes |
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.
Site Collection URL | Number of Owners |
URL1 | 1 |
URL2 | 2 |
URL3 | 1 |
URL4 | 5 |
URL5 | 1 |
URL6 | 1 |
URL7 | 1 |
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
Try this :
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.
@Patryk - See below. I tested the formula, works as you requested..
Please tag as a solution if this works for you. Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |