Count rows by column that contains more values in other column

[Anonymous]

Hello, 

I have a specific case here that I cannot move on and I have read through a few of forums.

I have a table of users with groups. Every user can have more groups - so 1 user is on more rows.

I need to count rows by users if in group column exist 2 special groups (group1 and group2).

See example below: user A has group1 and group2 - I want this user into selection

user B has only group1 - I do not want this user into selection

user C has only group2 - I do not want this user into selection

So I created the measure:

Special_Group =
VAR Group1Count=
CALCULATE(
COUNT('user groups'[User Account ID]),
FILTER(
'user groups',
SEARCH("Group1",'user groups'[GroupName],,blank())>0
)
)

VAR Group2Count=
CALCULATE(
COUNT('user groups'[User Account ID]),
FILTER(
'user groups',
SEARCH("Group2",'user groups'[GroupName],,blank())>0
)
)
RETURN
IF((IF(Group1Count>=1,1,0)+IF(Group2Count>=1,1,0))>=2,1,0)

easily - IF sum of Group1Count + Group2Count >=2 then 1 else 0

So I have now in powerbi  table:

User Account ID | Special Group

A | 1

B | 0

C | 0

but I need to COUNT these Users together - how many users with SpecialGroup I have - and I cannot SUM or COUNT the SpecialGroup. It says Total 1 - even if there is 100 users. I cannot use the measure in Legend also.

I wanted to add this measure as new Column - but I cannot do this also.

Could you please advise?

Thank you

[amitchandak · Accepted solution]

@Anonymous , Create a new column like

new column =
var _1 = countx(filter(Table,[user] =earlier([user]) && [group] = "Group1"),[user])
var _2 = countx(filter(Table,[user] =earlier([user]) && [group] = "Group2"),[user])
return
if(not(isblank(_1)) && not(isblank(_2)),1,0)

[Anonymous]

Thank you very much for superquick and easy answer. I adapt your formula to this and this works like a charm!

new column =

var _1 = countx(filter(Table,[user] =earlier([user]) && SEARCH("Group1",[group] ,,blank())>0),[user])

var _2 = countx(filter(Table,[user] =earlier([user]) && SEARCH("Group2",[group],,blank())>0),[user])

return

if(not(isblank(_1)) && not(isblank(_2)),1,0)