Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to Solution.
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))
Proud to be a Super User!
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.
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.
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))
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
81 | |
67 | |
61 | |
46 | |
45 |