Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table which has a number of columns where a person can be listed, e.g.
The individual can be listed as either the project owner or in any of the "Assisting Staff" columns.
I wish to be able to count the number of projects an indivual has been associated with, i.e. if they are listed on one project as the "Project Owner" and on another as "Assisting Staff #1" to have a count of '2'
Solved! Go to Solution.
You could do something like a calculated column in your Person's table like this:
LEAN Project Count = VAR __Table = ALL(LEANSavings) VAR ProjectOwner = COUNTROWS(FILTER(__Table,__Table[ProjectOwner]='People'[Person])) VAR Assist1 = COUNTROWS(FILTER(__Table,__Table[AssistingStaff1]='People'[Person])) VAR Assist2 = COUNTROWS(FILTER(__Table,__Table[AssistingStaff2]='People'[Person])) VAR Assist3 = COUNTROWS(FILTER(__Table,__Table[AssistingStaff3]='People'[Person])) VAR Assist4 = COUNTROWS(FILTER(__Table,__Table[AssistingStaff4]='People'[Person])) RETURN ProjectOwner + Assist1 + Assist2 + Assist3 + Assist4
In this case, 'People' is the name of your people table and [Person] is the column with your first and last name in it. Again, this would be created as a calculated column in your "People" table.
Do you have a central "Person" table or no?
I do indeed have a "person" table
I had tried to add a relationship between them, however you are only allowed one relationship between tables (for obvious reasons)
OK, So I am able to get a total count for a single individual based on the below, however I need this to go through the "User Table" and make a count based on each individual.
How would I be able to make the below reference the other table column?
LEAN Project Count = VAR ProjectOwner = CALCULATE( COUNTA(LEANSavings[BriefDescriptionOfLEANProject]), LEANSavings[ProjectOwner] = "forename surname" ) VAR Assist1 = CALCULATE( COUNTA(LEANSavings[BriefDescriptionOfLEANProject]), LEANSavings[AssistingStaff1] = "forename surname" ) VAR Assist2 = CALCULATE( COUNTA(LEANSavings[BriefDescriptionOfLEANProject]), LEANSavings[AssistingStaff2] = "forename surname" ) VAR Assist3 = CALCULATE( COUNTA(LEANSavings[BriefDescriptionOfLEANProject]), LEANSavings[AssistingStaff3] = "forename surname" ) VAR Assist4 = CALCULATE( COUNTA(LEANSavings[BriefDescriptionOfLEANProject]), LEANSavings[AssistingStaff4] = "forename surname" ) RETURN ProjectOwner + Assist1 + Assist2 + Assist3 + Assist4
I think what I am saying is that I would like it to be something like the below (but the below does not work):
VAR ProjectOwner = CALCULATE( COUNTA(LEANSavings[BriefDescriptionOfLEANProject]), LEANSavings[ProjectOwner] = 'Employee Name'[forenamesurname] )
You could do something like a calculated column in your Person's table like this:
LEAN Project Count = VAR __Table = ALL(LEANSavings) VAR ProjectOwner = COUNTROWS(FILTER(__Table,__Table[ProjectOwner]='People'[Person])) VAR Assist1 = COUNTROWS(FILTER(__Table,__Table[AssistingStaff1]='People'[Person])) VAR Assist2 = COUNTROWS(FILTER(__Table,__Table[AssistingStaff2]='People'[Person])) VAR Assist3 = COUNTROWS(FILTER(__Table,__Table[AssistingStaff3]='People'[Person])) VAR Assist4 = COUNTROWS(FILTER(__Table,__Table[AssistingStaff4]='People'[Person])) RETURN ProjectOwner + Assist1 + Assist2 + Assist3 + Assist4
In this case, 'People' is the name of your people table and [Person] is the column with your first and last name in it. Again, this would be created as a calculated column in your "People" table.
Can I use this to count number of rows/Line items for each individual ? Also , If i need to add a condition to the same for eg count number of rows for each individual where column x says "Yes" this was create a measure for all individuals where column x says "yes"?
TIA
That is fantastic and works great. Thank you very much Greg.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |