Count unique ids conditional on another column

[chulpanvl]

Hello everyone!

I have a table with «user id» column which contains the participants of lotteries and «event type» column which is an id of a lottery. "Event type" takes values from 0 to 3. A user can participate in the lottery multiple times. I want to get a percentage of users participated in the lottery «3» who also participated in at least one of other lotteries (0,1,2). The solution must be simple but somehow I cannot make it work. Hope to get some hints here.

Thank you!

[v-jingzhang · Accepted solution]

Hi @chulpanvl 

You can try this measure 

UserCount 3 & Other = 
var user_type_count_table = SUMMARIZE('Table','Table'[user_id],"Number_Of_Types",DISTINCTCOUNT('Table'[event_type]))
var user_type_3 = SUMMARIZE(FILTER('Table','Table'[event_type]=3),'Table'[user_id])
var user_type_3_and_other_count = COUNTROWS(FILTER(user_type_count_table,[Number_Of_Types]>1&&[user_id] IN user_type_3))
return
user_type_3_and_other_count

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@chulpanvl , Try a measure like

countx(filter(summarize(Table, Table[User], "_1", countx(filter(Table, Table[lottery] =3),Table[lottery]), "_2", countx(filter(Table, Table[lottery] in {1,2,0}),Table[lottery])), not(isblank(_1)) && not(isblank(_2)) ), [User])

[chulpanvl]

Thank you @amitchandak! For some reason it counts every user id, not just filtered by "not(isblank(_1)) && not(isblank(_2))" condition. Could you pls help to elaborate it? Here is a pbix file to test.