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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
InterSimi
Helper II
Helper II

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.

 

  1. Project Owner
  2. Assisting Staff #1
  3. Assisting Staff #2
  4. Assisting Staff #3
  5. Assisting Staff #4

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'

1 ACCEPTED 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

Do you have a central "Person" table or no?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.