Forum Discussion

InterSimi's avatar
InterSimi
Icon for Helper II rankHelper II
8 years ago
Solved

Count names in more than one column

I have a table which has a number of columns where a person can be listed, e.g.   Project Owner Assisting Staff #1 Assisting Staff #2 Assisting Staff #3 Assisting Staff #4 The individual can...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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.

InterSimi's avatar
InterSimi
Icon for Helper II rankHelper II
8 years ago

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]
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

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.

  • InterSimi's avatar
    InterSimi
    Icon for Helper II rankHelper II
    8 years ago

    That is fantastic and works great. Thank you very much Greg.

  • anshul1123's avatar
    anshul1123
    Icon for Helper I rankHelper I
    6 years ago

    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