Forum Discussion

PowerBI4Life's avatar
PowerBI4Life
Frequent Visitor
3 years ago

How To Count Distinct States Based Distinct PersonsNames

Hi!  I have two columns in a table.  State and PersonsName.  I want to calculate the count of distinct states that contain more than 10 distinct PersonsName.   Almost like how a GROUP BY in...
"Need Help"
Distinct Counts
MAwwad's avatar
MAwwad
Solution Sage
3 years ago

You can use DAX formula to achieve this in Power BI. Here's an example measure that counts the distinct states that have more than 10 distinct PersonNames:

 
Distinct States with >10 PersonNames = COUNTROWS( FILTER( SUMMARIZE( Table1, Table1[State], "DistinctPersonNames", DISTINCTCOUNT(Table1[PersonsName]) ), [DistinctPersonNames] > 10 ) )

Here, we use the SUMMARIZE function to group the data by State and calculate the number of distinct PersonNames for each State. Then we use the FILTER function to include only those rows where the count of distinct PersonNames is greater than 10, and finally use the COUNTROWS function to count the number of distinct States that meet this condition.

Replace "Table1" with the name of your actual table and "State" and "PersonsName" with the names of your actual columns.