Distinct count across all categories, not just within each

[sparker22]

Hi,

I am looking to create demographic breakdown of individuals, however Distinct Count isn't working as I need for returning users whose category changes.

I want all categories to equal the total number of unique users, rather than the total of unique users in each age

User#	Age
1	18
2	19
3	23
4	27
2	19
5	17
6	23
1	19

Because user#1's age changes, this will get counted once in both the 18 and 19 age category, rather than only once as I need because the user# is the same.

Any thoughts?

[Samarth_18]

Hi @sparker22 ,

Create a concatenated column as below and take a distinct count of it:-

Column = [User#]&"-"&[Age]

Measure = distinctcount(table[column])

Thanks,

Samarth

[sparker22]

Thanks for your response Samarth.

I believe this has the same issue.

As it stands there are 6 distinct users

If we get the distinct count of each age, user 1 will be counted twice, in both the 18 and 19 ages. I would like them to be only counted once

[Samarth_18 · Accepted solution]

@sparker22 , If I am took your question correctly then you can try:-

Measure 3 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table (3)'[User#] ),
    All ('Table (3)'[Age] )
)

[Jihwan_Kim]

Hi,

I am not sure which visualization you want to use to display your information, but please try the below.

expected outcome measure: =
CALCULATE (
    DISTINCTCOUNT ( TableName[User#] ),
    REMOVEFILTERS ( TableName[Age] )
)