Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PowerBI4Life
Frequent Visitor

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 SQL would work.

 

Any ideas on how to accomplish this? 

Thank you!

2 REPLIES 2
AilleryO
Memorable Member
Memorable Member

Hi,

 

I made a PBI with diffrent options for your questions.

I made a table with 2 columns (States and PersonName) and gave you 5 options :

1/ In Power Query by making a Group By. Good option if you don't need you data in details. See my first query for that option.

2/ Using DAX, you can make a basic calculation but your totals will be wrong, because of the lack of filter context on totals.

NbPersonDAX_Mesure = DISTINCTCOUNT(TableStatesPersonDAX[PersonName])
Works for every lines of your table except for the total.
3/ Using DAX with variable, works on the rows of the table but gives not total, since not current state available :
NbPersonDAX_Mesure v2 = 
VAR CurrState=SELECTEDVALUE(TableStatesPersonDAX[States])
RETURN
CALCULATE(
    DISTINCTCOUNT(TableStatesPersonDAX[PersonName]),
    TableStatesPersonDAX[States]=CurrState)
A little better since you do not have a wrong total, but maybe you need the total ?
4/ The solution I would recommend would be :
NbPersonDAX_Mesure v3 = SUMX( VALUES(TableStatesPersonDAX[States]) , 
                            CALCULATE( DISTINCTCOUNT( TableStatesPersonDAX[PersonName] ) ) )
Do not forget the CALCULATE before DISTINCTCOUNT because it provides the context transition needed in this case. Making the right count on the rows and the good total at the bottom of your table.
Or even better, test the formula with and without the CALCULATE to see by yourself the context transition.
5/ Create a summarized table in DAX with a filter :
TableDAX_withFILTER = FILTER( SUMMARIZECOLUMNS(TableStatesPersonDAX[States], "NbPersonStates" , 
DISTINCTCOUNT(TableStatesPersonDAX[PersonName])) ,[NbPersonStates]>2 )
If you need to display or make many calculations on those synthesis, could be an option as well.
Hope this will help you and others,
 
Please find attached demo pbix
MAwwad
Super User
Super User

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.