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 do indeed have a "person" table

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

I had tried to add a relationship between them, however you are only allowed one relationship between tables (for obvious reasons)

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

    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
    • 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.