Calculating Unique Values in a Dataset across columns

[sharpedogs]

Hi, 

Below is the dataset I have, it's a list of unique users and the AD groups they are in. I want to calculate how many people ONLY have Teams. That is if a user has both Teams and Exchange i don't wnat to include them.  Then i want to calculate how many people ONLY have both Teams and Exchange.

I want to show that

Teams only users are 2 (Sam and David)

Teams and Exchange Only Users are 2 (Matt and John)

Chad	Teams
Chad	Office
Chad	Exchange
David	Teams
Matt	Teams
Matt	Exchange
Sam	Teams
John	Teams
John	Exchange

[Anonymous]

[# Users With Teams Only] =
var __result =
	sumx(
		values( T[Name] ),
		calculate(
			selectedvalue( T[ADGroup] ) = "Teams"
		)
	)
return
	if( __result > 0, __result )
	
	
[# Users With Teams and Exchange] =
var __result =
	sumx(
		values( T[Name] ),
		calculate(
			var __groups = values( T[ADGroup] )
			return
				countrows( __groups ) = 2
				&&
				countrows(
					intersect(
						__groups,
						{"Teams", "Exchange"}
					)
				) = 2
		) * 1
	)
return
	if( __result > 0, __result )

Whether the above does what you need or not depends on what you really want... Check it out and if not, then tell me where it goes wrong. Thanks.

Best

D

[sharpedogs]

Hey, 

I am getting the following error. It doesn't like the SUMX function in it's current format?

MdxScript(Model) (8,3) calculation error in Measure "AD Groups'[Users with Teams Only]: The function SUMX cannot work with values of type Boolean.