Getting a distinct count of users who have all possible values of a column

[mtm2006]

Hi everyone, I have a table with two columns: containing the names of individuals and their group type.

The three possible values of Type are:  [A, B, C]

An individual can have one, two or all three of the type values. Also, the individual can have the same type multiple times.
For example, Alex has the value A and B both two times, Catherine has the value B two times, and David has the value C three times.

I would like to get a count of distinct users who have all three values of Type. Can this be done?

In this case, the output is 2, as only Alex and David each have all possible values from the Type column.

Thanks for your help!

[ryan_mayu · Accepted solution]

@mtm2006 

maybe you can try to create a measure

Measure = 
var tbl=SUMMARIZE('Table','Table'[User],"type num",count('Table'[Type]))
return COUNTROWS(FILTER(tbl,[type num]=3))

 

[v-xuxinyi-msft · Accepted solution]

Hi @mtm2006 

You can try the following steps:

1. Add an index column to a table in the Power Query.

If the index column is not added, the effect in the table visualization is shown below.

2. Create several measures as follow.

alltypeuser = 
VAR _ut = CALCULATETABLE(DISTINCT('Table'[Type]), ALLEXCEPT('Table', 'Table'[User]))
RETURN
IF( COUNTROWS(_ut) = 3, 1, 0)

countalltypeuser = CALCULATE(DISTINCTCOUNT('Table'[User]), FILTER('Table', [alltypeuser] = 1))

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@mtm2006 

maybe you can try to create a measure

Measure = 
var tbl=SUMMARIZE('Table','Table'[User],"type num",count('Table'[Type]))
return COUNTROWS(FILTER(tbl,[type num]=3))