If/And Measure? Or Filter?

[forti4040]

Hello All,

I have one column of Projects, two columns of Names, and two columns of Pay in my data set.

Column A = Project Name

Column B = Lead Person

Column C = Support Person

Column D = Lead Pay

Column E = Support Pay

I'd like to write a measure that finds someone's name in both Column B and Column C and sums the appropriate Pay from Columns D & E. 

Example

Project ABC     Bob    Sam     $10     $20

Project XYZ     Tim     Tom     $15     $30

Project GEF     Sam    Tim      $50     $75

If I search for Tim, the sum of the pay should be $90 (15 + 75). Additionally, if I filter my table view I'd like to see only the projects that Tim shows up in either Column B or Column C. 

Any help is appreciated!

[vicky_]

Here's my attempt at solving that problem - 
I first created another table with the names of all the staff - 

All Staff = DISTINCT(UNION(VALUES('Table (2)'[Lead]), VALUES('Table (2)'[Support])))

This table is disconnected from the original table.

And then I wrote a measure to grab the sum of the pay for each role: 

Sum of Pay = 
var summaryTable = UNION(
    SELECTCOLUMNS('Table (2)', "name", 'Table (2)'[Lead], "pay", 'Table (2)'[Lead Pay]), 
    SELECTCOLUMNS('Table (2)', "name", 'Table (2)'[Support], "pay", 'Table (2)'[Support Pay])
)
return SUMX(FILTER(summaryTable, [name] = SELECTEDVALUE('All Staff'[Lead])), [pay])

I then used the names from the table I created, and then used the measure to create the results table.

Lastly, for the table filtering requirements, I recommend you take a look at this article that goes more in-depth on slicer filtering: https://apexinsights.net/blog/or-xor-slicing

[forti4040]

Hello @vicky_ ,

Thank you for the detailed reply!

I follow most of what you've laid out but have a couple questions. 

1. I already have a separate table with each of the peoples names in it (listed once each). So I think I can skip your first step, correct?
   1. I do not have unique lists of names for Lead and Support. There is one source of names, and those can either be populated in Lead or Support in the data source. So my name column would apply to both. 
2. For the Measure "Sum of Pay" is that in my main table or in the one you created separately?
   1. You mentioned that your new table is disconnected so I'm wondering if I'm just not putting the measure in the right place. Right now I created the measure in my original table. 
   2. When I use the values in a table it is not adding them correctly.
      1. My assumption is that something I've done with the name column is driving this. 
3. For the filtering link you sent me I guess I have the same question about creating separate tables. Since I already have a separate table with a single list of names in it, do I need to duplicate that table and reference both tables as shown in your link?

Thank you again for the help on this!

[vicky_]

Hey!

1. Yes, if you already have a list of staff, there's no need to create a new table for that. Just use the name in that table instead of the AllStaff Table I have above. The point of me creating a table with unique names is so that I can grab the pay regardless of if the staff is lead or support. 

2. Measures (as opposed to calculated columns) don't depend on the table that they're in, but rather the row and column dimension(s) that are in the visual. So actually, it doesn't matter which table you put the measure in. (Actually, before power BI implemented folders, some people would create an empty table to use as a folder to organise their measures)

3. And again, if you already have a list of names, then you don't need to create a new table for that. Just use the one you already have.

Hope that helps.

[forti4040]

Thank you Vicky,

Understood on placement of measures. I've adjusted my work and am still having some issues with the calculations. And it could be how I'm trying to visualize the data. 

If I filter my page by a persons name, I would expect the below table to reflect the Total Pay for that individual based on their being lead or support. 

So if I use my Name Table to filter this page by Sam, I would want to see the below.  

Project Name	Lead Person	Support Person	Lead Pay	Support Pay	Total Pay
Project ABC	Bob	Sam	10	20	20
Project XYZ	Sam	Tim	50	75	50

Right now when I filter using this name I do not get this result. The closest I've gotten is having the Lead Pay shown in Total Pay for both rows. 

Regards,

Eric

[forti4040]

@vicky_  just following up to see if you have any ideas on this last piece? I'm still unable to get the solution I'm looking for, though I appreciate all your help so far!