Calculate Distinct Count of Filtered Values if bigger than other value

[Patryk]

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.

Correct end values for the above example would be:

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

[tjlundquist1]

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]

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]

@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