topic Re: count of distinct two columns in one table in DAX Commands and Tips

count of distinct two columns in one table

91asma2 — Thu, 11 Mar 2021 22:04:13 GMT

Hi all,
I am trying to calculation the unique number of users who did 2 or more activities from two separate columns in one table.
My data looks like this:

Table

user_id	activity_id
1	aa
2	aa2
3	aa3
4	aa4
5	aa5

First I want to get the distinct count of users and then filter those users that have done 2 or more activities.
I am trying something like this, but I am not getting the values right.

VAR allData =
ADDCOLUMNS(
VALUES(Table[activity_id]),
"rowCount",
CALCULATE( COUNTROWS( Table) )
)
return
CALCULATE(
COUNTROWS( allData ),
filter (allData, [rowCount] >=7)
)

I appreciate all the help.

Re: count of distinct two columns in one table

amitchandak — Fri, 12 Mar 2021 03:38:51 GMT

91asma2 , Try a measure like

countx(filter(summarize(Table, Table[user_id], "_1", count(Table[activity_id])),[_1]>=2),[user_id])

Re: count of distinct two columns in one table

91asma2 — Fri, 12 Mar 2021 16:57:23 GMT

Thanks a lot amitchandak !
Can you explain to me this forumla, more walk me thought it?

I am trying to validate the values. and I am trying to change the filter from 2 to 1 to 3 ...etc but the number doesnt change.

so I am not sure its counting the right values.

What I am trying to get the count of users who has 2 or more activities.