I have a business issue where some people are being provided the Microsoft Teams license and no other license. I am trying to do a quick view to easily capture these people.
I have a set of users with a list of AD Groups. If they are in the AD group they are assigned that license. In some cases a user may be in AD Group (Exchange) and AD Group (Exchange 1). They are still only given one license. To solve for this I created a new column using switch function that makes (Exchange and Exchange 1) the same.
I need a new column that shows me the people who are ONLY in the Teams AD Group. And another column that shows me the accounts that are ONLY in both Exchange and Teams groups.
Only in Teams AD Group =
VAR Teams = CALCULATETABLE( VALUES( 'AD Groups'[User] ), 'AD Groups'[AD Groups] = "Teams" )
VAR Other = CALCULATETABLE( VALUES( 'AD Groups'[User] ), 'AD Groups'[AD Groups] <> "Teams" )
RETURN
COUNTROWS(
EXCEPT( Teams, Other )
)
.
Only in Exchange and Teams AD Group =
VAR Teams = CALCULATETABLE( VALUES( 'AD Groups'[User] ), 'AD Groups'[AD Groups] = "Teams" )
VAR Exchange = CALCULATETABLE( VALUES( 'AD Groups'[User] ), LEFT( 'AD Groups'[AD Groups], 8 ) = "Exchange" )
VAR Other = CALCULATETABLE( VALUES( 'AD Groups'[User] ), 'AD Groups'[AD Groups] <> "Teams", LEFT( 'AD Groups'[AD Groups], 8 ) <> "Exchange" )
RETURN
COUNTROWS(
EXCEPT(
INTERSECT( Teams, Exchange ),
Other
)
)
Wow – the timing on this couldn’t be better. I just released a video last week with a methodology that is a perfect fit to this problem. The video explains the logic in the solution below. Note that David does not get picked up as being in Exchange and Teams… At first I thought this was a flaw in my solution, and then in debugging realized that the formula was smarter than I was – Exchange is misspelled in David’s entry in your dataset, and so it doesn’t recognize him as being in the group.
Here’s the Teams and Exchange measure. The other one for Teams only follows the exact same logic structure.
In Teams Exchange Only =
VAR SelUser =
SELECTEDVALUE( 'AD Groups'[User] )
VAR MemberGroups =
CALCULATETABLE(
VALUES( 'AD Groups'[Revised AD Groups] ),
'AD Groups'[User] = SelUser
)
VAR NumGroups = COUNTROWS( MemberGroups )
VAR InTwoGroup =
IF(NumGroups = 2, 1, 0)
VAR InTeams =
IF( "Teams" IN MemberGroups, 1, 0 )
VAR InExchange =
IF( "Exchange" IN MemberGroups, 1, 0 )
VAR Combined = InTwoGroup * InTeams * InExchange
RETURN
Combined
I love how you set it the VAR as (1 or 0) then had the return multiply them together… the logic is beautiful. I don’t use variables, but I think i need to start learning how to use them more. Is there an EDNA training session on Variables? I’ve done the basic DAX and i didn’t really see any mention of them.
I’ve watched you video as well. I’ll have to watch is a few more times before it sinks in…lol
My only question is why doesn’t the “Teams only” column show a total? In your PBX i would expect it to sum up a total of 2? The measures are whole numbers, i’m not sure why it doesn’t sum?
Man, I’m going to have to hire you as a plant to sit in the audience of my webinars and feed me all the right questions. I’ve got a video on THAT question coming at the beginning of this week.
The answer is that in calculating totals, DAX does so line by line not relative to the column of numbers above it. So in this case, it gets to the total line, doesn’t find any names in the evaluation context and thus all the dummy variables evaluate to zero (i.e., false). In order to force the total to calculate correctly you need to incorporate some additional logic using virtual tables to force the the total to sum the column of numbers above it. All explained in detail in the coming video, or you can go to @greg’s excellent DAX Patterns section of the forum focused on fixing incorrect totals:
I’ve got a video on THAT question coming at the beginning of this week.